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:
-
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.
-
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 snapshot isolation is enabled for
dbName, skip the remainder of
this procedure.
-
Issue the following command to enable snapshot isolation for dbName:
ALTER DATABASE dbName SET READ_COMMITTED_SNAPSHOT ON
-
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'