SQL Server lock and isolation levels

Concept:

Common lock-related concepts see locks in SQL Server

Isolation level:

read Uncommitted, read non-committed data

Read-Committed ,

1, Pessimistic mode (is_read_committed_snapshot_on=0, default setting), the traditional read-committed, can only read the data that has been submitted. Reading and writing can create conflicts.

2, Optimistic mode (is_read_committed_snapshot_on=1), add row version control, can only read the submitted data, read and write no conflict, concurrency is better, production environment needs testing.

Repeatable Read , one transaction, multiple reads of the same or a few data, see the result is the same

serializable, which is the highest isolation level, which resolves a phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

Snapshot Isolation , when the SNAPSHOT (snapshot) Isolation level is enabled, each time the row is updated, the SQL Server database engine stores a copy of the original row in tempdb and adds the transaction sequence number to the row.

Snapshots and MVCC

At present, most relational databases use 2PL protocol to ensure the serialization of concurrent transaction execution, which creates the problem of read-write mutex, that is, s lock and X-lock mutex.

The multi-version concurrency control (multi-version Concurrency CONTROL,MVCC) solves this problem better. In multiple versions of the system, each write data produces a new version, the read operation can read the appropriate version as needed, so read and write operations are not blocked. MVCC, while increasing concurrency, also brings storage overhead for maintaining multiple versions.

The Microsoft SQL Server Database engine introduces a new implementation of the existing transaction isolation level-read-committed to provide statement-level snapshots that use row versioning. The SQL Server Database engine also introduces a new transaction isolation level-a snapshot that provides transaction-level snapshots that also use row versioning.

The version store is a collection of data pages in tempdb. If more than one transaction modifies a row, multiple versions of that row are linked to a version chain. A read operation that uses row versioning retrieves the last version of each row that was committed when the transaction or statement started.

SQL Server lock and isolation levels