Introduction to 6 transaction isolation levels in SQL Server

The source of this article: http://www.cnblogs.com/wy123/p/7218316.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

The things in the database are four characteristics that have atomicity (atomicity), consistency (CONSISTEMCY), isolation (isolation), persistence (durability).
In the implementation of the consistency and isolation of the four attributes above, the access isolation of the same data is achieved through the lock.
The isolation level of a thing can also affect the timing of the lock's application and timing.
As a result, different isolation levels can have different effects on the application and release of locks, so you need to understand the impact of isolation levels on things when you are doing things to control the database.
In addition to the four isolation levels (uncommitted read, read-committed, Repeatable read, serialized) that SQL Server implements for the SQL99 standard, there is an additional two isolation levels (snapshot of the read-committed isolation level based on the row version).
Different isolation levels to control dirty read, non-repeatable read, Phantom Reading has a certain degree of control, also can have a certain extent of influence,
The lower the isolation level, the higher the concurrency, but the more likely it is to produce dirty reads, non-repeatable reads, and Phantom reads; With the submission of the object isolation level, you can control dirty reads, non-repeatable reads, and Phantom reads, but concurrency also decreases.
The object isolation level and execution plan can all affect the lock (range) request and release time, this article does not discuss the impact of the execution plan on the lock request, and only describes the lock request and release at the isolation level.
The following is a brief description of the six isolation levels in SQL Server and the characteristics of each isolation level, based on which you can resolve the problem that may exist for each isolation level.

Non-committed read

Run the current session to read the data that the other transaction has modified but not yet committed, that is, the current session can read "dirty data".
The current session does not add shared locks to the data being read.
Set TRANSACTION isolation level READ UNCOMMITTED;
Or
SELECT * FROM table with (NOLOCK)

Features: Uncommitted reads are the lowest level of isolation.
Existing problems: Dirty reading, inconsistent reading, phantom reading, etc.

The following is a demonstration of the presence of uncommitted degrees of dirty reading.

Read Committed

Set TRANSACTION ISOLATION level Read Committed;
Or
SELECT * FROM table default person is read Committed
Running the current session cannot read data that has been modified but not yet committed by another transaction.
The current session is blocked if other transactions have modified the data read by the current session and have not yet committed it.
The reason is that at the read-committed isolation level: The current session will share the lock on the read data, and if the read data has not yet been committed, the current query is blocked.

Features: resolves read "Dirty data" under uncommitted read isolation level compared to read-committed isolation level,
Problem: There is an issue of non-repetition or phantom reading.

Non-repeatable read at Read Committed isolation level (inconsistent with the same row of data results that were read two times)

Non-REPEATABLE read Phantom read at isolation level (in one thing, the same condition, the number of rows of data that is read is inconsistent)

REPEATABLE READ

Set TRANSACTION ISOLATION level repeatable read;
Running the current session cannot read data that has been modified but not yet committed by another transaction, and the other session cannot modify the data read by the current session while the current session is running
That is, during the current session, the data read is shared, and the shared lock is held until the transaction commits.
The most common feature of read-committed reads is that the shared locks remain until the current session is committed, while the transaction is running.
Therefore, it is possible to keep the data read by the current session from being modified by other sessions, so there is no inconsistency of data that is read two times.
The REPEATABLE read isolation level solves the problem of non-repeatable reads because the shared lock of the first query remains at the end of the transaction during the current session execution.
During this time, other sessions cannot modify the data read by the current session, so repeatable reads can be achieved.

Features: Repeatable read solves the non-repeatable read of read-committed isolation levels compared to the previous isolation level, or the same row of data that is read two times is consistent
Problem: There is still a problem of phantom reading compared to the read submitted.

The following is a repeatable read of the isolation level of the Phantom read phenomenon, that is, during two reads of the same thing, other things can be written to the current thing read data (range)

　　

Serializable

The current session cannot read other modified but uncommitted data from the session (dirty reads are not allowed)
The shared lock on the data read by the current session remains until the transaction commits (Repeatable Read)
The other session cannot insert the key value read in the current session until the current session transaction is committed (resolves the problem of phantom reading)
Set TRANSACTION ISOLATION level serializable
Or add holdlock to the table after you turn on the transaction
SELECT * FROM table with (HOLDLOCK) where id = n
Serializable solves another very classic problem, using the Update table with (HOLDLOCK) or select * from table with (Xlock,holdlock), in the case of "presence update does not exist" insert The problem of repeated insertions.
Reference: http://www.cnblogs.com/TeyGao/p/6929246.html

The principle of serializable locking is implemented by adding range locks, and when a session initiates a request, a shared lock is added to the data in the current session range, whether or not it exists.
For example, under the Serializable isolation level, select * from table with where id>=100 and id<= 120
During session execution, SQL Server locks the data in the range of 100<=id<= 120, regardless of whether there is data in the table, locks the scope of the ID, and does not allow data to be written to that ID range.
Also known as 100<=id<= 120 This range is locked (unable to add data to delete or modify this range)

The Serializable isolation level solves the problem of Phantom reading, that is, the two reads of the current thing, the other session of the current session to read data within the range of data modification, will be blocked until the current thing submitted.

Row versioning-based isolation level

The default isolation level, or read Committed isolation level, there is an obvious problem is that the write will block the read, that is, a write data thing is not committed before, it will block other things on the current operation of the data read, until the current write action commit.
Under read-committed isolation levels based on row versioning, writes are not blocked from reading, and writing data before a thing is committed, the modified version of the data is written to the staging database,
When the reading of the data is read, it is found that the data to be read is modified, and it is shifted to the temporary library to read a version of the object before it modifies the data, thus improving concurrency to some extent (of course, the temporary library will bear some pressure).
SQL server has two row versioning-based isolation levels: Snapshot Isolation Level (snapshot) and row versioning-based read-committed isolation level (READ_COMMITTED_SNAPSHOT)
Two row versioning to open Allow_snapshot_isolation and read_committed_snapshot based on data level, respectively

(1) Snapshot Isolation level (snapshot)

Database level set Snapshot isolation Level
ALTER DATABASE Test set allow_snapshot_isolation on;

Session level Set Snapshot Isolation levels: Set TRANSACTION isolation Level snapshot

The most important feature of the snapshot isolation level is that when the current session reads data modified by other things, it will not be blocked, read something else has been modified, but the data that has not yet been submitted
But the current thing is trying to modify the "submit modified data in other things" and will report an error.
The process is as follows, from the time dimension, the steps are as follows
1) Session2 Open Things, modify the data of ID =1, do not submit
2) Session1 read id=1 data, will not be blocked, read the Session2 modified before the version of the data
3) After Session2 modifies the data of ID =1, the thing commits
4) Session1 Attempt to modify id=1 data, error

　　

In practical operation, see the following

(2) Read-committed Isolation level (READ_COMMITTED_SNAPSHOT) based on row versioning

Database level set to read-committed isolation level based on row versioning
ALTER DATABASE Test set READ_COMMITTED_SNAPSHOT on;
Go

--Set the current object to the committed read Snapshot isolation level
Set TRANSACTION ISOLATION LEVEL Read Committed

The most important feature of the snapshot isolation level is that when the current session reads data modified by other things, it will not be blocked, read something else has been modified, but the data that has not yet been submitted
Relative to the snapshot isolation level, the current session attempts to modify the "submit modified data in other sessions", which can be successfully submitted.
The process is as follows, from the time dimension, the steps are as follows
1) Session2 Open Things, modify the data of ID =1, do not submit
2) Session1 read id=1 data, will not be blocked, read the Session2 modified before the version of the data
3) After Session2 modifies the data of ID =1, the thing commits
4) Session1 Attempt to modify the data of Id=1, successfully submitted,

The most significant feature of the read-committed isolation level based on row versioning is that the data currently being read is that the other session has been modified before the previous version has been committed, but when the current thing tries to modify it, it can successfully commit
In this way, the data that is modified and submitted by other things is ignored while the current thing is running, which is a bit of a detour and needs to be understood slowly.

The problem with the read-committed isolation level of row versioning is also obvious, when the current session reads the data, it is the version of the other things before the change, the current session to read the data can be in the other things before the revision of the price,
While ignoring the current session during the reading and writing interval, other sessions modify and commit the effects of things, this may have a certain degree of impact.

Look at the time dimension as shown

Specific implementation phenomena are as follows:
The problem is that the first time Session1 read, read the ID = 1 of the data is AAA, in fact, at this time the other Session2 has changed the ID = 1 in order to UPDATE_AAA,
Then Session2 things submitted, the current session to perform the modification, ignoring the Session2 modified data, you can directly modify the data to aaa+++
It is important to note that the Session1 modification succeeds if the Session2 is committed, and if Session2 modification is not committed, the Session1 modification operation is blocked.

The comments in the first line are not modified, should be the snapshot has been raised to read the isolation level

　　

Summarize:

This article provides a brief description of the isolation levels in SQL Server, where SQL Server implements the four standard isolation levels defined by SQL99 and an additional two snapshot isolation levels.
It should be explained that the default isolation level of the different DBMS is not exactly the same as the implementation of the isolation level, or it is not necessarily the same as the four standard isolation levels defined by SQL99,
Therefore, when doing things control, you need to understand the specific isolation level and specific characteristics.

Introduction to 6 transaction isolation levels in SQL Server