sql transaction lock snapshot (forwarded, very well written)

The isolation level defines the degree of isolation of transaction data read operations. In SQL Server, the isolation level only affects the shared lock (Shared Lock) applied for the read operation, but does not affect the exclusive lock (Exclusive Lock) applied for the write operation. Isolation levels control the behavior of read operations:

• Whether to use shared locks when reading data and what type of locks to apply for;
• The time the transaction holds the read lock;
• Control the behavior of read operations when they reference data rows that have been updated by other transactions but have not yet been committed:
  • Blocked, waiting for other transactions to release the mutex lock;
  • Get the data value before the update, read the row version from tempdb, the row version has been committed when the transaction started; Retrieves the committed version of the row that existed at the time the statement or transaction started.
  • Read uncommitted data and obtain updated data values;

When performing a write operation, the transaction always holds the mutex lock and is not released until the end of the transaction. The mutex lock is not affected by the transaction isolation level. In SQL Server, mutex locks are incompatible with any locks. At the same time, only one transaction can hold a mutex lock on the same data row. That is to say, write operations are performed sequentially and completely isolated. Cannot be executed concurrently. Isolation and concurrency go hand in hand.

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an 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. Use the SET command to modify the isolation level of Session-Level, 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

1. Transaction isolation level

The default transaction isolation level of SQL Server database level is Read Committed. Users cannot modify the default isolation level of Database-Level. However, users can modify the default transaction isolation level of Session-Level. The default transaction isolation level of Session-Level is Read Committed. This isolation level is affected by the database option READ_COMMITTED_SNAPSHOT, which determines whether the Read Committed isolation level uses row versioning to control transaction read operations or uses shared locks to control transaction read operations. Under the default Read Committed isolation level:

• If the option READ_COMMITTED_SNAPSHOT is set to OFF, the transaction applies for a shared lock when performing a read operation, blocking the write operations of other transactions;
• If the option READ_COMMITTED_SNAPSHOT is set to ON, the transaction will use Row Versioning when performing read operations, will not apply for shared locks, and will not block the write operations of other transactions;

Under any isolation level, a transaction applies for an exclusive lock when performing a write operation, and holds the exclusive lock until the end of the transaction. The mutex lock is not controlled by the isolation level; while the shared lock (Shared Lock) is isolated Level control, isolation level affects the application and release of Shared Lock:

• Under the Read Uncommitted isolation level, read operations will not apply for Shared Lock;
• Under Read Committed (without row-versioning), Repeatable Read and Serializable isolation levels, Shared Lock will be applied for;
• Under the Read Committed (without row-versioning) isolation level, when the read operation is executed, the Share Lock is applied for and held; once the read operation is completed, the Shared Lock is released;
• Under the Repeatable Read and Serializable isolation levels, the transaction will hold the Shared Lock until the end of the transaction (commit or rollback);
• Under the Serializable isolation level, the transaction will hold the range Shared Lock (Range Lock), which locks a range. While the transaction is active, other transactions are not allowed to perform update (Insert or delete) operations in the range;

SQL Server supports the isolation level of Row Version. The read operation of the transaction only applies for the SCH-S table-level lock, and does not apply for the Page lock and Row lock. The modification operation of the transaction still applies for the lock:

• When the database option READ_COMMITTED_SNAPSHOT is set to ON, the Read Committed isolation level uses Row Version to provide Statement-Level read consistency;
  • When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.
• The Snapshot isolation level uses Row Version to provide Transaction-Level) read consistency. At the beginning of the current transaction, any read operation is based on the same database snapshot. When reading data rows modified by other transactions, get row version data from tempdb. When using the Snapshot isolation level, the database option ALLOW_SNAPSHOT_ISOLATION must be set to ON;
  • When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started.
• Under the snapshot and read committed snpshot isolation levels, the data read by the transaction is committed;
• Note that statement-level read consistency and transaction-level read consistency are the biggest differences between snapshot and read committed snpshot:
  • Transaction-level read consistency means that the transaction holds a snapshot of the data from the beginning of the transaction to the time the transaction is committed. If other transactions update table data during the transaction activity, the transaction will only read the snapshot data and will not read the data values updated by other transactions;
  • Statement-level read consistency means: the data seen by a single statement (single statement) is consistent; during the current transaction activity, statements in the transaction can read data values submitted and updated by other transactions; for example, in When the statement stmt1 is executed, the transaction does not commit the update, and stmt1 sees that the value of Reader1 is 2; when the statement stmt2 is executed, the transaction commits the update, and stmt2 sees that the value of Reader2 is 3;

Second, use the isolation level of Row Version

Under the default isolation level Read Commited, when performing a read operation, the transaction applies for a shared lock, and the read and write operations block each other. Under the isolation level Read Uncommitted, transactions will not apply for shared locks, so read operations will not block write operations, but read operations may read dirty data. Dirty data refers to the data value after being modified by other uncommitted transactions, not the data value before the update.

The row version refers to the data stored in tempdb, containing the data row and TSN. A Data Row in a data table can have multiple Row Versions. When a modification operation occurs, SQL Server creates a Row Version, copies the Original Row to the Row Version, and stores the TSN of the current transaction in the Row Version. Therefore, Row Version stores the data value before modification.

SQL Server provides Snapshot isolation level for reading data values before modification. Under the Snapshot isolation level, before a transaction modifies any data, it first copies the original data rows to tempdb and creates an original version (Row Version) of the data rows. Note that SQL Server will only copy the modified data rows. For unmodified data rows, rows of data, row version data will not be saved. Subsequent read operations of other transactions will read the copied row version. Under the Snapshot isolation level, read and write operations will not block each other. Using row version control can improve the concurrency of transactions, but there is an obvious shortcoming. Although the user does not read dirty data, the data may be being modified soon. It’s about to expire. If data modification is made based on this expired data, logic errors may occur.

1. Enable Snapshot isolation level

Set the database option ALLOW_SNAPSHOT_ISOLATION to ON, without changing the transaction isolation level of Session-Level. You need to modify the transaction isolation level of Session-Level to SNAPSHOT to use row version data.

alter database current
set allow_snapshot_isolation on;

When using the Snapshot isolation level, the isolation level of the current Session must be set to Snapshot. Only in this way can the current transaction access Row Versioning data:

set transaction isolation level snapshot

2. Database option READ_COMMITTED_SNAPSHOT (referred to as RCS)

Under the default isolation level Read Committed, to enable transactions to access Row Versioning data, the database option READ_COMMITTED_SNAPSHOT needs to be set to ON:

alter database current
set allow_snapshot_isolation on;

alter database current
set read_committed_snapshot on;

The prerequisite is that the database option ALLOW_SNAPSHOT_ISOLATION must be set to ON; Once the RCS option is enabled, in the default Read Committed isolation level, transactions access versioned data rows. Under the RCS isolation level, transactions have two characteristics:

• Transactions use row version (Row version) instead of locking, and read operations will not block write operations of other transactions;
• The RCS isolation level ensures transaction consistency at the statement level. The query statement can only read the data that has been submitted when the statement is executed. If the data update has not been submitted when the statement is executed, the statement cannot be read;

3, READ COMMITTED Snapshot isolation level

Under the Read Committed isolation level, a transaction cannot read data that has been modified by other transactions but has not yet been committed, that is, it can only read data that has been submitted for update. The behavior of the READ COMMITTED isolation level is affected by the database option: READ_COMMITTED_SNAPSHOT:

• If you set the RCS option to OFF (the default setting), the database engine uses Shared Lock to prevent other transactions from modifying the data being read by the current transaction; when reading data rows that have been modified by other transactions but have not yet committed the update, the read operation will be block; block;
  • If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions 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 is completed.
• If the RCS option is set to ON, the database engine uses row versioning (Row Versioning) data to achieve statement-level consistency. It will not block write operations of 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 transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

3. Enable snapshot isolation level

1. Use snapshot isolation level

step1, set database options

ALTER DATABASE CURRENT SET SINGLE_USER<br>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 Session-Level to snapshot

set transaction isolation level snapshot

2, use Read_Committed_Snapshot isolation level

ALTER DATABASE CURRENT SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE CURRENT SET MULTI_USER;

Four. Citing the example of teacher Xu Haiwei, testing isolation level behavior

The snapshot isolation level will not block the write operations of other transactions. This isolation level ignores data modification operations and only reads row versioning data. That is to say, what is read is the version before the data modification. When the snapshot transaction attempts to modify the data modified by other When the data modified by the transaction occurs, an update conflict occurs and the write operation terminates abnormally.

Read committed snapshot isolation level, read row versioned committed data:

• When other transactions have not committed updates, read the row versioned data, that is, read the data value before modification;
• After other transactions submit data updates, read the modified data values;
• Since this isolation level does not apply for shared locks, it does not block update operations of other transactions;
• Ability to update data modified by other transactions;

Five, Snapshot isolation level (MSDN translation)

Under the SNAPSHOT isolation level, any write operation will save the data row before the update to tempdb, and the read operation will either read data from the Original Database data table or read row version data from tempdb. Snapshot isolation level specifies: In a transaction, the data read by any statement is a transactionally consistent version. Transaction consistency means that at the beginning of a transaction, a data snapshot is created at the table level and only data updates that have been committed by other transactions can be recognized. After a transaction begins, the current transaction does not recognize data updates performed by other transactions. The Sanpshot isolation level achieves transaction-level data consistency. SQL Server uses tempdb to store row versioning data. If the data is updated frequently and too many row versions are stored, tempdb will become a system bottleneck.

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that 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 other transactions after the start of the current transaction are not visible to statements executing 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.

1. Under the Snapshot isolation level, the update operation creates a Row Version

Once the Snapshot isolation level is enabled, when an update operation is performed in a transaction, SQL Server will store the original version of the updated data row in tempdb, that is, save the Original data of the data row in tempdb, therefore, read the row version of the data, You can only read the value before the data row is updated. Each transaction has a unique, increasing sequence number, recorded as TSN (Transaction Sequence Number). TSN can uniquely identify a transaction. Each row version stores a TSN, which identifies the transaction that created the row version.

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.

2. Snapshot isolation achieves transaction consistency

The Snapshot isolation level implements transaction-level data consistency, which means that all query statements in a single transaction see the same version of data. Under the Snapshot isolation level, transactions do not need to add row-level locks or page-level locks when reading data, and read and write operations do not block each other.

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.

3. Snapshot uses optimistic concurrency mode

The Snapshot isolation level uses optimistic concurrency mode. If a Snapshot transaction attempts to submit an update to a data row, but the data row has been modified by another transaction, and the modification time is earlier than the start time of the current transaction, then SQL Server will treat the current transaction as a failure. , and rolls back its transaction operations. Optimistic concurrency mode is used in environments with fewer conflicts. If the Application frequently conflicts when updating data, the Snapshot isolation level may not be the best choice.

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.

4. Snapshot isolation and Row Version working modes

When the Snapshot isolation level is enabled, each operation that updates data will store an original copy of the row in tempdb. The term is called row version (RowVersion). SQL Server adds the TSN of the transaction to each row version, which can uniquely identify it. The transaction in which the update operation occurs. When reading data, the read operation is performed in the following order:

• Create a new transaction and assign it a TSN, a unique, increasing sequence number;
• The snapshot transaction reads data rows from the data table and reads the row version (row version) from tempdb. The TSN of this row version is closest to the TSN of the current transaction, but smaller than the TSN of the current transaction;
• When creating a Snapshot, obtain the row version data from the committed transaction. If the transaction identified by the row version data has not yet been committed, obtain the submitted updated data from the earlier transaction;
• The transaction reads row version data from tempdb. The transaction will not see the newly inserted data because the TSN of the inserted data is larger than the TSN of the current transaction;
• Transactions can see data deleted by other transactions, provided that the TSN of the transaction that deleted the data is greater than the TSN of the current transaction. This is because other transactions save row versions to tempdb, and the current transaction reads row version data from tempdb;

When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. The following is the sequence of events that occurs:

• A new transaction is initiated, and it is assigned a transaction sequence number.

• The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

• The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

• The transaction reads the version of the row from tempdb that was current as of the start of the transaction. It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

• The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without holding or placing any locks on the underlying tables. This can result in performance improvements in situations where there is contention.

A snapshot transaction always uses optimistic concurrency control, with holding any locks that would prevent other transactions from updating rows. If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

Reference documentation:

Snapshot Isolation in SQL Server

Isolation Levels in the Database Engine

SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level

Author:
Happy time

Source:
http://www.cnblogs.com/ljhdo/