Clients who have very high transaction volume might encounter some SQL deadlock issues. In these cases, Aptify recommends using snapshot isolation to significantly reduce or eliminate database blocking issues in high-volume situations. The purpose of this topic is to provide information about how to configure the SQL Server transaction isolation level.
SQL Server 2005 added two new isolation levels, Read Committed Snapshot and Snapshot, to enhance concurrency for OLTP applications. These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency that statements and transactions experience. In earlier versions of SQL Server, concurrency was based solely on locking, which caused blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios. All of these isolation levels are described in the following Microsoft Development Network article:
The Snapshot Isolation Level and Read Committed Snapshot features improved SQL Server, providing optimistic locking as well as pessimistic locking. This is a feature that allows writers not to block readers, and readers will not block writers. Instead, the readers will look at the most recent row, and ignore the fact that it’s currently being written to. This will help, in that long running transactions won’t block other transactions.
Read Committed Snapshot is a modification to the Read Committed Isolation level that uses row versioning to read the previous value. Turn on Read Committed Snapshot, and a lot of your blocking will be a thing of the past. Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.
Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified. Search for "Locking Hints" for your version of SQL Server for more information.
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb). You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration.
Perform this procedure to enable SQL Server snapshot isolation. Ensure that you understand the information in the Understanding SQL Server Snapshot Isolation Levels before going ahead and enabling snapshot isolation.
Run the following query to enable the READ_COMMITTED_SNAPSHOT option:
Run the following query to enable the ALLOW_SNAPSHOT_ISOLATION option:
Run the following query to verify the values of these options: