We are developing a high performance/throughput application using Hibernate 5.2 and SQL Server Standard 2016. The application is processing data in multiple threads that access the same sets of tables. We are unfortunately experiencing huge slowdowns and locking issues due to the fact, that Hibernate sends directly or indirectly (through JDBC driver) SET IMPLICIT_TRANSACTIONS ON statement every time we want to access the data, even for read only processing.
Our transaction strategy is as follows:
- Read all the data to memory (without transaction, @Transactional(Propagation.NEVER) is used to ensure that.
- Process the data on the application side
- Start the transaction, save the results and commit right away.
Unfortunately even with NEVER propagation and with autoCommit being set to TRUE on Hibernate properties level, we are still having implicit transactions being created even on step 1, when we are trying to enforce the application to not create a transaction.
This is a serious problem for us and I’ve seen some people on the Internet were experiencing that too, but there is no answer to be found. Can you please advise? As far as we know (from our dbas) SQL Server is by default supposed to work in an autoCommit enabled and should never work in Transaction, unless one is explicitly started.