Snapshot isolation level of the database (Snapshot isolation)

The isolation level defines the degree of isolation of the transaction operation resource and the update data, in SQL Server, where the isolation level affects only the shared lock requested by the read operation, and does not affect the mutex for the write operation request. The isolation level controls when a transaction performs a read operation:

• Whether a shared lock is used when reading data, and what type of isolation level is requested;
• Time when a transaction holds a read lock
• The read operation controls the behavior of the read operation when it references data rows that are updated by other transactions:
  • is blocked, waiting for other transactions to release the mutex;
  • Reads the version of the transaction commit, which exists at the beginning of the transaction; retrieves the committed version of the row that existed at the time the statement or transaction s tarted.
  • Read the data without submission;

When a write operation is performed, the transaction holds the mutex until the end of the transaction is released, and the mutex is not affected by the transaction isolation level. Isolation and concurrency are the relationships that have been extended. In SQL Server, mutexes and arbitrary locks are incompatible, and at the same time, only one transaction holds the mutex on the same data row, that is, the write operation is sequential and cannot be concurrent.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets a exclusive lock on any data it modifies, and holds that lock until the transaction completes, Regardless of the isolation level set for that transaction.

There are 5 isolation levels for transactions, modify the isolation level of session-level using the SET command, and use DBCC USEROPTIONS to view the isolation level of the current session:

SET TRANSACTION Isolation  level     READ Uncommitted    | READ COMMITTED    | Repeatable READ    | SNAPSHOT     | SERIALIZABLE DBCC Useroptions

View Code

One, the isolation level of the transaction

The default isolation level for SQL Server is read Committed, and users cannot modify the Database-level default isolation level, but can modify the Session-level default isolation level. Session-level The default isolation level is read Committed, which is affected by the database option Read_committed_snapshot and determines whether the read Committed isolation level uses row versioning transactions. Under the Read Committed isolation level:

• If the SET option Read_committed_snapshot is off, the transaction requests a shared lock while performing the read operation, blocking the write operation of the other transaction;
• If the SET option Read_committed_snapshot is on, the transaction uses row Versioning when performing a read operation, does not request a shared lock, and does not block the write operation of other transactions;

At any isolation level, a transaction requests a mutex (exclusive lock) when performing a write operation, holds the mutex until the end of the transaction, the mutex is not controlled by the isolation level, and the shared lock is controlled by the isolation level, and the isolation level affects shared Lock's application and release:

• Read does not apply for shared Lock under the READ UNCOMMITTED isolation level;
• In read Committed (without using row-versioning), the repeatable read and serializable isolation levels will apply for shared Lock;
• Under Read Committed (no row-versioning) isolation level, apply and hold share lock when reading is performed, and once the read operation is complete, the shared lock is released;
• Under repeatable Read and serializable isolation levels, transactions hold shared Lock until the end of the transaction (commit or rollback);

SQL Server supports using the isolation level of row versioning, the read operation of a transaction only requests a sch-s table-level lock, and does not request a page lock or row lock:

• When the database option Read_committed_snapshot is set to On,read COMMITTED the isolation level uses row versioning to provide read consistency for the statement level (Statement-level) ;
  • When a transaction runs at the Read Committed isolation level, all statements see a snapshot of data as it exists at the S Tart of the statement.
• The snapshot isolation level uses row Versioning to provide read consistency for transaction-level (transaction-level) . When reading a data row modified by another transaction, gets the row version data at the beginning of the current transaction, and when using the SNAPSHOT isolation level, the database option must be set allow_snapshot_isolation to on;
  • When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction Started.
• Note that the read consistency at the statement level and the transaction-level read consistency are the biggest differences between the snapshot and the Read Committed Snpshot:
  • Read consistency at the transaction level refers to the transaction holding a snapshot of the table data at the start of the transaction and during the transaction commit. If the table data is updated by other transactions during the transaction activity, the transaction reads only the snapshot data and does not read the data values that are updated by other transactions;
  • Read consistency at the statement level refers to the snapshot of the data held by the transaction, which is released immediately at the end of the statement, and the ability to read the updated data value during the transaction activity, while the other transaction is committed;

Second, use the isolation level of row versioning

At the default isolation level read commited, the transaction requests a shared lock while the read operation is being performed, and the read and write operations are blocked from each other. Under the isolation level read UNCOMMITTED, the transaction does not request a shared lock, so the read operation does not block the write operation, but the read operation may read dirty data. Dirty data refers to data values that have been modified by other uncommitted transactions, not the data values that were previously updated.

SQL Server provides the snapshot isolation level for reading data values before modification. Under the snapshot isolation level, the transaction copies the pre-modified data to tempdb before modifying any data, and the write operation creates an original version of the data row (row version), noting that SQL Server replicates only the modified rows of data, and for unmodified rows of data, Row version data is not saved. All subsequent read operations of the other transaction are read to the copied row version. Read and write operations do not block each other under the snapshot isolation level. Using row versioning increases the concurrency of transactions, but there is an obvious drawback, although the user is not reading dirty data, but the data may be being modified and will expire soon. If data modifications are made based on this outdated data, a logic error can occur.

1, enable snapshot isolation level

Set database option allow_snapshot_isolation to ON, no change to session-level transaction isolation level, need to modify SESSION-LEVEL transaction isolation level to SNAPSHOT, To use row version data

Alter Database  Current Set on;

To use the snapshot isolation level, you must modify the isolation level of the current session snapshot so that the current transaction can access the row versioning data:

SET TRANSACTION Isolation  Level SNAPSHOT

2, database option Read_committed_snapshot (referred to as RCS)

When using row-versioning data, the database option Allow_snapshot_isolation must be set to ON, the database option Read_committed_snapshot set to ON, and the default isolation level read Committed, transactions can access the row versioning data:

Alter Database  Current Set  on ; Alter Database  Current Set on;

The premise is that the database option must be set allow_snapshot_isolation toon, and once the RCS option is enabled, in the default read Committed isolation level, the transaction uses the row version (row versioning) Instead of locking, transactions can access versioned rows of data. The read Committed snapshot Isolation Level guarantees statement-level transactional consistency, and the TSQL statement can only read data that has been committed at the beginning of the statement. The read operation of the current transaction does not block writes for other transactions, and the current transaction reads data values that have been modified by other transactions.

When a transaction runs at the Read Committed isolation level, all statements see a snapshot of data as it exists at the S Tart of the statement. Setting the read_committed_snapshot on option allows access to versioned rows under the default READ COMMITTED isolation L Evel.

3,read COMMITTED Snapshot Isolation Level

Under the Read Committed isolation level, transactions cannot read data that has been modified by other transactions but have not yet committed, that is, only the data that has been committed is read, and the behavior of the read Committed isolation level is subject to the database option: Read_committed_ Effects of Snapshot:

• If the RCS option is set to OFF (the default), the database engine uses shared lock to prevent other transactions from modifying the data being read by the current transaction, and the read is blocked when the read is modified by another transaction but the updated data row has not yet been committed;
  • If Read_committed_snapshot is set to OFF (the default), the Database Engine uses GKFX locks to prevent other transaction s from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction are Completed.
• If you set the RCS option to on, the database engine uses row Versioning data to implement statement-level consistency, does not block writes for other transactions, but can only read data that has been submitted for updates
  • If Read_committed_snapshot is set to ON, the Database Engine uses row versioning to present each statement with a Transact ionally consistent snapshot of the data as it existed at the start of the statement. Locks is not used to protect the data from updates by other transactions.

4, Snapshot Isolation Level

The SNAPSHOT isolation level specifies that the data read in one transaction is a consistent version of the data. At the beginning of a transaction, a data snapshot is created at the table level and only data updates submitted by other transactions are recognized. The current transaction does not recognize data updates performed by other transactions after the start of the transaction. The Sanpshot isolation level enables transactional-level data consistency. SQL Server uses tempdb to store row versioning data, which can cause tempdb to become a system bottleneck if the data is more updated and the row versions are stored too much.

Specifies that data read by any statement in a transaction would be the transactionally consistent version of the data existed at the start of the transaction. The transaction can only recognize data modifications, that were committed before the start of the transaction. Data modifications made by and transactions after the start of the current transaction is not visible to statements exe Cuting in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the Transaction.

Third, enable snapshot isolation level

1, using snapshot isolation Level

Step1, setting Database options

 alter  database  current  set  single_user 
 with  rollback   IMMEDIATE;  alter  database  current  set  Allow_snapshot_isolation on   -- alter DATABASE current SET read_committed_snapshot OFF;  alter  database  current  set  Multi_user; 

Step2, modify the isolation level of the session-level to snapshot

Set Transaction Isolation  Level Snapshot

2, using Read_committed_snapshot isolation Level

ALTER DATABASE  Current SETSingle_user with ROLLBACKIMMEDIATE;ALTER DATABASE  Current SETAllow_snapshot_isolation on; ALTER DATABASE  Current SETRead_committed_snapshot on; ALTER DATABASE  Current SETMulti_user;

Four, referring to the example of Xu Haiwei teacher, test the behavior of isolation level

The snapshot isolation level does not block writes for other transactions, which ignores the modification of the data and reads only the row versioning data, that is, the version that was read before the data was modified, and when the snapshot transaction attempted to modify data modified by another transaction. An update conflict occurs and the write operation terminates abnormally.

Read Committed snapshot isolation level, reading the row-versioned submitted data:

• When other transactions do not commit the update, read the row-versioned data, which reads the data value before the modification;
• After the other transaction commits the data update, reads the modified data value;
• Because the isolation level does not request a shared lock, update operations for other transactions are not blocked;
• Ability to update data modified by other transactions;

Reference Documentation:

Isolation levels in the Database Engine

SQL server–difference between Read Committed Snapshot and Snapshot isolation level

Snapshot isolation level of the database (Snapshot isolation)