KB-1263 Deadlock errors with SQL Server

Symptoms

In the application server log you will see the following sets of errors indicating deadlocks:

org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
...
<Error> <org.hibernate.util.JDBCExceptionReporter> <BEA-000000> <Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.> 
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Depending on how the database is currently set to handle deadlocks, CPU utilization may be abnormally high as the system works to resolve the deadlocks.

Cause

Locking is enforced by the database to handle race conditions where multiple users/processes are attempting to access the same data within a database at the same time. While the default handling of locks may be suitable to ensure data integrity, it comes at the cost of system performance, as each user/thread must wait until the lock is “broken” before it can access the data it needs to.

Action

To get around this we can enable SQL Server to use “snapshots” of its data. Speak to your Database Administrator about enabling the following settings within your database:

SET ALLOW_SNAPSHOT_ISOLATION ON;
SET READ_COMMITTED_SNAPSHOT ON;

READ_COMMITTED_SNAPSHOT:

This creates a snapshot, a read-only copy of the database, that is separate from your live database. When you run a SELECT statement with this setting enabled, you are reading from the snapshot. When you change your database, the change is effective on the live database, and a new copy or snapshot is created later for reading.

ALLOW_SNAPSHOT_ISOLATION:

When this option is turned ON, the instance of the Microsoft SQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. If there are active modification transactions, SQL Server sets the state of the option to PENDING_ON. After all of the modification transactions complete, the state of the option is changed to ON. Users cannot start a snapshot transaction in that database until the option is fully ON.

Affected Versions

This article applies to all versions of Appian.