CODEWITHSUNDEEP
sql-serverspringlockinghint
Dan Angelov
Dan Angelov

Reputation: 1

Choosing the right hints during select for update in SQL Server

We have a Spring app that uses SQL Server for a database with a Hibernate dialect: org.hibernate.dialect.SQLServer2012Dialect that extends org.hibernate.dialect.SQLServer2005Dialect

We decided and then implemented only one type of locking (pessimistic read) for all updates of our central JPA entity in transactions with the default isolation - READ_COMMITTED. The implementation itself consists of transactional service methods that retrieve for update the entities calling repository methods annotated with: @Lock(LockModeType.PESSIMISTIC_READ) We did that to prevent silent lost updates. Was it the right choice?

After a while we noticed that a lot of org.hibernate.exception.LockAcquisitionException / org.springframework.dao.CannotAcquireLockException were thrown. According to SQLServer2005Dialect the entities were retrieved for update with hints (holdlock, rowlock) and the whole table was locked for updates during each transaction that retrieves more than one entity for update. We wonder whether to implement our own Hibernate dialect extending SQLServer2012Dialect and overriding with another hints when retrieving the entities for update. Are we on the right track?

The combination of hints (updlock, rowlock) does not satisfy us without further configuration because after testing we noticed that retriving one rows for update blocks retrieving another rows for update.

What hints to supply in the queries and is there additional configuration needed?

This is the desired behavior:

Upvotes: 0

Views: 123

Answers (1)

SQLpro
SQLpro

Reputation: 5169

You are completely wrong about the manner to use locking in a RDBMS. Using hints is the worst thing to do because you statically impose the lock while SQL Server automatically chooses it in the most appropriate way and in addition dynamically (it can change the granularity of the lock during a transaction because it is capable of doing so : "lock escalation").

So let it naturally make the best choices. SQL Server is equipped with one of the best optimizers in the world of Relational DBMS.

For your requests, enable optimistic locking mode with the command:

ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

and force pessimistic locking using the SERIALIZABLE isolation level for UPDATEs

Upvotes: 0

Related Questions