SQL Server Transaction ISOLATION LEVEL

Reference Documentation:

Https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

https://msdn.microsoft.com/zh-cn/library/jj856598 (v=sql.120). aspx

One, the transaction isolation level controls the following behavior of the transaction:

Whether the lock is occupied while reading the data and the type of lock requested.

The time that the read lock was occupied.

Read operations that refer to rows modified by other transactions:

• Blocks other transactions before the exclusive lock on the row is freed.
• Retrieves the committed version of the row that existed when the statement or transaction was started.
• READ UNCOMMITTED data modifications.

The above indicates that the transaction isolation level is primarily for read operations.

Second, the difference between dirty reading, repetition reading and phantom reading:

It is not necessary to mention these 3 concepts when it comes to the isolation level of things, and it can be said that the level of transaction isolation is to avoid these 3 situations.

Dirty read: Read data that has been modified but not committed by other transactions

Non-REPEATABLE READ: Two queries in the same transaction read different data

Phantom read: Two queries in the same transaction read a different number of records

Some people may not understand the definition of phantom and non-repeatable reading, the biggest difference is essentially the difference between the lock, there will be explained behind.

The Ansi/iso standard defines the following transaction isolation levels, and the SQL Server database engine supports all 4 isolation levels:

So the four isolation levels correspond to dirty reads, Phantom reads, non-repeatable reads as follows:

It is important to note that although MySQL and Oracle support the same level of transaction isolation as the ANSI standard, there are significant differences:

• Oracle only supports read-committed and serialized reads.

• MySQL's default repeatable read isolation level avoids Phantom reads with range locks.

Iv. In addition to the above 4 isolation levels, SQL Server also supports the other two transaction isolation levels using row versioning:

• One is the snapshot implementation under the default Read Committed isolation level, strictly not a transaction isolation level, just a special form of Read Committed.
  

• One is the new transaction ISOLATION level----Snapshot isolation level.

The two are opened in the following ways:

1. If you want to turn on the SNAPSHOT transaction isolation level, you need to pre-set allow_snapshot_isolation to ON and only modify the session-level transaction isolation level at this time.

ALTER DATABASE [dbname] SET allow_snapshot_isolation on; --Need to be modified in single-user mode because an exclusive lock is added to the library level.

Then execute the following statement to modify the transaction isolation level: (modified only at the session level and cannot modify the transaction isolation level at the global level)

SET TRANSACTION isolation level{READ uncommitted| READ committed| Repeatable read| Snapshot| SERIALIZABLE}

2. Using Read_committed_snapshot, the following ALTER statement modification is performed directly, and is modified under the default READ COMMITTED isolation level, which is permanently enforced after modification, using DBCC Useroptions view you can see that the transaction isolation level has been modified globally to read Committed snapshot.

ALTER DATABASE [dbname] SET read_committed_snapshot on;

The difference between the two is:

Read_committed_snapshot refers to a SELECT statement that always reads the most recent committed data, that is, if a DML transaction is executing, the SELECT statement is not blocked but the pre-generated image of those DML transactions is read. This reading only adds Sch-s locks to the table, and all other row lock page locks are not. Once the DML data is committed, executing the SELECT statement again will immediately read the new data.

The difference between the snapshot isolation level and the above is that if you execute the same SELECT statement two times within the same transaction, even if data changes occur between the two SELECT statements and the data is committed, two reads the same.

Using the official website to describe the difference between the two is:Read_committed_snapshot provides statement-level consistency, snapshot transaction isolation level provides transaction-level consistency.

Five, all 6 isolation levels of the lock mode:

began to say that the transaction isolation level is mainly to control the read operation plus what locks, locks take a long time, so only to understand the isolation level of each transaction lock mechanism to completely understand the concept of transaction isolation level and their differences.

1. non-submitted read

uncommitted reads do not lock the read data, so there is no blocking and dirty reads appear, which is equivalent to adding the WITH NOLOCK option to the SELECT statement.

2. Read-Committed

Read-committed data is normally locked for read, but the lock is not released until the end of the transaction, but it is freed when a page is read, so duplicate reads and phantom reads may occur. This is the default transaction isolation level for SQL Server and Oracle. Oracle's implementation is more perfect, and the select operation is not locked for the Oracle database, so there is no read blocking write, but SQL Server is possible.

3. A read snapshot has been submitted

SQL Server-specific isolation levels, primarily to match the capabilities of Oracle's committed read implementations, read operations only add a sch-s lock to the table at this isolation level, so read operations do not cause blocking, but will increase the use of tempdb.

4. Snapshots

As above, the read operation only adds sch-s locks, the only difference being that the consistency read is the transaction level, that is, the snapshot is retained in tempdb for a longer period of time.

Here it is assumed that the revocation of the snapshot under committed read Snapshot isolation level is after the DML transaction ends, and the snapshot at the snapshot read isolation level is revoked after the select transaction ends.

5. Repeatable Reading

A repeatable read lock is exactly the same as a committed read, except that the lock is freed only after the entire transaction is complete, rather than after a page is read, and this locking method avoids non-repeatable reads because other DML cannot acquire locks on the resource during the transaction.

6. Serialization of Read

There is a difference between a serialized read lock and a committed read, and this isolation level reads a key-range lock on the index key instead of the normal S, U, X, is, IU, IX, and so on.

The mechanism of the key range lock is basically the same as the range lock in MySQL, mainly to prevent the Phantom reading, its mechanism is that the select operation will not only lock the Read key value, but also lock the range of the upper and lower key values.

Examples are as follows:

There is a record of the primary key for 1,5,8,9,10, select ... where col between 3 and 7; A key-range lock is used to lock the 5 record, and a key-range lock is used to lock the 346 unreal records so that no data can be inserted during the read operation. , you can prevent Phantom reads.

Ps: For the serialization plus the key range of the lock is not so accurate as I said above, but also need specific experiments, here is just about guessing will use the extra key range lock lock may cause phantom reading records, specific experimental methods see my other blog, interested can try.

Http://www.cnblogs.com/leohahah/p/7059852.html

SQL Server Transaction ISOLATION LEVEL