The first thing we did was to switch to READ_COMMITED_SNAPSHOT isolation level. It helped to some extent, but the locking was still an issue. Just to be clear - we are using auto-commit only on the reading part. After that, when we want to persist and save the data, we are using usual Spring Transaction management and wrapping everything in Transactions.
Please explain to me why do we need an active Transaction while reading data, if we are not intereseted in increasing the isolation to REPEATABLE_READ or SERIALIZABLE? We are also not selecting for update so we don’t need to lock what we’ve read. We have our own case-specific distributed locking to ensure that no 2 parallel processes are touching the same data.
You are very quickly jumping into conclusions and judging design that you haven’t even seen. Believe me, the auto-commit is the last resort for us, the design was changed drastically and we weren’t able to fully resolve locking issues.