Set Transaction Isolation Levels
You must set the transaction isolation levels to READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to avoid read locks in highly concurrent environment.
The snapshot isolation level specifies that data read within a transaction does not reflect changes made by another simultaneous transactions. The transaction uses the data row versions that exist when it begins. Therefore, when the data is read, no locks are placed and the snapshot transactions do not block other transactions from writing data. To enable the snapshot isolation levels, specify the following two properties:
- READ_COMMITTED_SNAPSHOT: Set the READ_COMMITTED_SNAPSHOT database option to ON to provide statement-level read consistency. The statements cannot read data values that are modified, but not yet committed by other transactions.
To set this parameter, log in as admin and run the following script with an appropriate database name:
ALTER DATABASE DATABASENAME SET READ_COMMITTED_SNAPSHOT ON
By default, the READ_COMMITTED_SNAPSHOT database option is set to OFF.
- ALLOW_SNAPSHOT_ISOLATION: Set the ALLOW_SNAPSHOT_ISOLATION database option to ON to provide transaction-level read consistency. If another transaction modifies the reading rows, the Microsoft SQL Server database engine instance retrieves the version of the row that existed at the start of the transaction. You can only use Snapshot isolation against a database.
To set this parameter, log in as admin and run the following script with an appropriate database name:
ALTER DATABASE DATABASENAME SET ALLOW_SNAPSHOT_ISOLATION ON
By default, the ALLOW_SNAPSHOT_ISOLATION is set to OFF.
For the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION levels, the read operations acquire only the Schema Stability (Sch-S) table level locks. It does not lock any pages or rows.
For more information to set these transaction isolation levels, refer to the Microsoft site (http://msdn.microsoft.com/en-us/library/ms173763.aspx).