JdbcMetadataStore putIfAbsent may stuck in while loop on MySQL database.

[jurek7]

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

6.5.7

Describe the bug

I observed that JdbcMetadataStore stuck in while loop on my production environment(2 service instances) in the following method:

https://github.com/spring-projects/spring-integration/blob/main/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/metadata/JdbcMetadataStore.java#L227

Issue may happen where two processes(or threads) will access putIfAbsent method at the same time and one of them was able to insert record to database.

Issue is related to the fact that MySQL database is using REPEATABLE READ isolation level and if one process(or thread) successfully insert record to database the other will not be able to read it thus it will stuck in while loop forever.

To Reproduce

Run two services with MySQL database and default transaction isolation level.

Expected behavior

Should work in distributed environment and MySQL database.

Sample

I was able to simulate issue with tests containers and unit tests but I am not able to copy this code here due to strict security policy in company. Pseudo code to reproduce problem:

for(int idx = 0; i<100; i++) {
exec = Executors.newFixedThreadPool(2)
futures = new ArrayList(2)
futures.add(CompletableFuture.supplyAsync(() -> metadataStore.putIfAbsent("testKey" + idx, "testValue")), exec)
futures.add(CompletableFuture.supplyAsync(() -> metadataStore.putIfAbsent("testKey" + idx, "testValue")), exec)
CompletableFuture.allOff(futures).join();
exec.shutdown();
exec.awaitTermination(1, TimeUnit.SECONDS);
}

[artembilan]

I think this needs to be rephrased somehow in your report:

Expected behavior

Shouldn't work in distributed environment and MySQL database.

It is also would be great to have more symptoms: like sole logs and exceptions to determine how that loop n

It is not clear what really you are expecting from us to fix.
However we are opened for any suggestions.
And contribution is welcome!

On the other hand, let's see if SERIALIZABLE isolation helps somehow in your case.

Thanks

[jurek7]

Rephrased.

In this method there is no logging so I am not able to provide.

I observed that problem in AWS RDS console. There were INSERT and SELECT queries executed with rate ~1000 query/second.

INSERT query: https://github.com/spring-projects/spring-integration/blob/main/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/metadata/JdbcMetadataStore.java#L106

SELECT query: https://github.com/spring-projects/spring-integration/blob/main/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/metadata/JdbcMetadataStore.java#L79

I override this bean locally and set READ_COMMITED isolation level in Transactional annotation on putIfAbsent absent method and it works as expected.

[artembilan]

I override this bean locally and set READ_COMMITED isolation level in @Transactional annotation on putIfAbsent absent method and it works as expected.

Please, learn how to comment properly on GH: https://docs.github.com/en/get-started/writing-on-github/getting-started-with-writing-and-formatting-on-github/basic-writing-and-formatting-syntax.

Just using @ is a call for that GH user into our conversation.

I don't think we need to override anything in the framework, but just use that @Transactional(isolation = Isolation.READ_COMMITTED) on the service method where you call that JdbcMetadataStore.putIfAbsent().

You probably also may consult with that AWS RDS how to change an isolation level by default.

What exactly as a fix you want to see in the framework?

[jurek7]

We are not calling this method directly from application so it is hard to find a place where it should be added.

Changing default isolation level might be dangerous for whole application and I do not think that this is the right way to solve this problem.

I think that spring-integration should work with common database like MySQL.

I think that while-true loop was develop with assumption that READ_COMMITTED transaction isolation level is being used so I you can force it in annotation parameter as I did to workaround the problem.

[artembilan]

We are not calling this method directly from application so it is hard to find a place where it should be added.

Right. You might use a JdbcMetadataStore from some channel adapter like SFTP polling.
You can find a transaction setting over there and, therefore, set isolation level.

I still don't mind to fix that loop in the putIfAbsent(), but we need to have some clue what exactly to react on.
The annotation is not the way to go since transaction should be controlled outside of that method level.