The 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 to significantly reduce or eliminate database blocking issues in high-volume situations.
...
Anchor _under _under
Understanding Snapshot Isolation Levels
| _under | |
| _under |
| Note | ||
|---|---|---|
| ||
We recommend setting the READ_COMMITTED_SNAPSHOT database option ON. If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data. In this topic also discusses setting ALLOW_SNAPSHOT_ISOLATION database option ON. This isolation state can be used but is not required as per Aptify's analysis and testing. |
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:
...
- Log into SQL server management studio with an sa-level user account.
- Open a new query.
Run the following query to enable the READ_COMMITTED_SNAPSHOT option:
No Format nopanel true /** Run this statement to change the isolation level of the database. When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode. **/ ALTER DATABASE Aptify SET READ_COMMITTED_SNAPSHOT ON;
Run the following query to enable the ALLOW_SNAPSHOT_ISOLATION option:
No Format nopanel true /** AlsoStatement suggestbelow tocan runbe thisused statementto toturn makeon availablethe snapshot isolation, so that transactions can set the snapshot isolation level. NotPer currently aware thatour analysis, there is anyno Aptify code that currently does this, therefore this is not required.**/ ALTER DATABASE Aptify SET ALLOW_SNAPSHOT_ISOLATION ON;
Run the following query to verify the values of these options:
No Format nopanel true /** Query to determine snapshot states **/ SELECT nameName , snapshot_isolation_state , snapshot_isolation_state_desc , is_read_committed_snapshot_on FROM sys.databases WHERE name = 'APTIFY';