JDBC Lock Registry not working with Oracle #8748

uebelack · 2023-10-04T20:24:26Z

In what version(s) of Spring Integration are you seeing this issue?

6.1.3

Describe the bug
Using JDBC Lock Registry on Oracle always throws java.lang.IllegalMonitorStateException: The current thread doesn't own mutex at ...

To Reproduce

 Lock lock = lockRegistry.obtain(foo);
        try {
            if (lock.tryLock(2, TimeUnit.MINUTES)) {
                specialService.special(foo, bar);
            } else {
                throw new RuntimeException("Lock not obtained");
            }
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        } finally {
            lock.unlock();
        }

Expected behavior

JDBC Lock Registry on Oracle does not throw exception.

Sample

https://github.com/uebelack/spring-integration-lock-oracle

Workaround

List.of("serializableTransactionTemplate", "defaultTransactionTemplate", "readOnlyTransactionTemplate").forEach(templateName -> {
            try {
                Field declaredField = DefaultLockRepository.class.getDeclaredField(templateName);
                declaredField.setAccessible(true);
                TransactionTemplate transactionTemplate = (TransactionTemplate) declaredField.get(lockRegistry);
                transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
            } catch (NoSuchFieldException | IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        });

The text was updated successfully, but these errors were encountered:

artembilan · 2023-10-05T17:39:48Z

Thank you for the detailed sample!

Really helpful.

So, looks like we cannot use a SERIALIZABLE isolation:

According to the docs, ORA-08177 can be caused only by serializable transactions. It means that a row which the serializable transaction is trying to modify was modified by another transaction after the serializable transaction has begun. Similar situation can happen with the read committed isolation level, but in this case the database automatically and silently restarts the entire SQL statement, and no error occurs.

And we need to change it to the ISOLATION_READ_COMMITTED only for the serializableTransactionTemplate.
I guess from here we also would need to rename that variable respectively 😉

Will be fixed soon...

artembilan · 2023-10-05T20:26:55Z

That is strange. Your application fails for me even with this simple code:

    @Test
    void power() {
		Lock lock = lockRegistry.obtain("foo");
		lock.lock();
		lock.unlock();
    }

The exception is this:

org.springframework.dao.CannotAcquireLockException: Failed to lock mutex at acbd18db-4cc2-385c-adef-654fccc4a4d8

	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.rethrowAsLockException(JdbcLockRegistry.java:202)
	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.lock(JdbcLockRegistry.java:196)
	at com.example.springintegrationlockoracle.OracleSuperServiceTest.power(OracleSuperServiceTest.java:44)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:147)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:127)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:90)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:55)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:102)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:54)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
	at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO INT_LOCK (REGION, LOCK_KEY, CLIENT_ID, CREATED_DATE)
VALUES (?, ?, ?, ?)
]; SQL state [72000]; error code [8177]; ORA-08177: can't serialize access for this transaction

I cannot reproduce it without Spring Boot in simple JUnit test for Spring Integration.

Probably HikariDataSource does something what effect transactions...

When I "mock" it with this:

	@TestConfiguration
	public static class TxConfiguration {

		@Bean
		public PlatformTransactionManager transactionManager() {
			DataSource dataSource =
					new DriverManagerDataSource(oracleContainer.getJdbcUrl(),
							oracleContainer.getUsername(),
							oracleContainer.getPassword());
			return new JdbcTransactionManager(dataSource);
		}

	}

It works well.

Either way I'm to go ahead and fix as we agreed before.

Fixes spring-projects#8748 The Oracle DB throws `ORA-08177: can't serialize access for this transaction` when other transaction on the row has begun * Change the isolation for `DefaultLockRepository.acquire()` transaction to the `READ_COMMITTED` for what database automatically and silently restarts the entire SQL statement, and no error occurs. * Add `oracle` dependencies to JDBC module * Introduce `OracleContainerTest` and implement it for `OracleLockRegistryTests` **Cherry-pick to `6.1.x` & `6.0.x`**

Fixes #8748 The Oracle DB throws `ORA-08177: can't serialize access for this transaction` when other transaction on the row has begun * Change the isolation for `DefaultLockRepository.acquire()` transaction to the `READ_COMMITTED` for what database automatically and silently restarts the entire SQL statement, and no error occurs. * Add `oracle` dependencies to JDBC module * Introduce `OracleContainerTest` and implement it for `OracleLockRegistryTests` **Cherry-pick to `6.1.x` & `6.0.x`**

uebelack added status: waiting-for-triage The issue need to be evaluated and its future decided type: bug labels Oct 4, 2023

artembilan added this to the 6.2.0-RC1 milestone Oct 5, 2023

artembilan added in: jdbc backport 6.0.x (EOL) for: backport-to-6.1.x and removed status: waiting-for-triage The issue need to be evaluated and its future decided labels Oct 5, 2023

artembilan mentioned this issue Oct 5, 2023

GH-8748: JDBC locks: use READ_COMMITTED isolation #8749

Merged

garyrussell closed this as completed in #8749 Oct 9, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

JDBC Lock Registry not working with Oracle #8748

JDBC Lock Registry not working with Oracle #8748

uebelack commented Oct 4, 2023 •

edited

Loading

artembilan commented Oct 5, 2023

artembilan commented Oct 5, 2023

JDBC Lock Registry not working with Oracle #8748

JDBC Lock Registry not working with Oracle #8748

Comments

uebelack commented Oct 4, 2023 • edited Loading

artembilan commented Oct 5, 2023

artembilan commented Oct 5, 2023

uebelack commented Oct 4, 2023 •

edited

Loading