Reducing deadlock errors in Microsoft SQL Server

High Microsoft SQL Server concurrency causes transaction deadlock errors because writers block access, by readers, to database resources. You can reduce the likelihood of deadlock by setting the READ_COMMITTED_SNAPSHOT ON option for your database.

Procedure

To reduce deadlock errors in a Microsoft SQL Server database:

  1. Shut down all the servers and clients that can connect to your database (dbName), and make sure that there are no other connections to Microsoft SQL Server.
  2. Connect to Microsoft SQL Server and issue the following SQL command to determine whether snapshot isolation is enabled for dbName: 
    SELECT name, is_read_committed_snapshot_on FROM sys.databases 
  where name='dbName'

    If column is_read_committed_snapshot_on returns 1, which means the snapshot isolation is enabled for dbName, skip the remainder of this procedure.

  3. Issue the following command to enable snapshot isolation for dbName: 
    ALTER DATABASE dbName SET READ_COMMITTED_SNAPSHOT ON
  4. Restart Microsoft SQL Server and issue the following SQL command to confirm that the Snapshot Isolation setting is in effect for dbName: 
    SELECT name, is_read_committed_snapshot_on FROM sys.databases 
  where name='dbName'