Today, I was helping a team that was getting a database deadlock in their Java Spring Boot application using Microsoft SQL Server. The stacktrace of the error was as shown below.
Exception is org.springframework.dao.CannotAcquireLockException: could not execute query; SQL ; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute query] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 132) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The database deadlock was caused when user uploads a file that was being written to the database. While the file was being uploaded user navigated to another page where data from the same table was queried.
When we debugged SQL Server traces we discovered that row-level lock was escalated to table lock. This was causing SQL Server to stop other transactions from accessing the table.
To solve this issue we enabled READ_COMMITTED_SNAPSHOT option for the database so that it uses READ_COMMITTED with row versioning isolation strategy. The default database isolation level in SQL Server is READ_COMMITTED which means a query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Since wee enabled READ_COMMITTED_SNAPSHOT SQL Server will use versioning instead of locks.
ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
