Lock-to-transaction isolation level in SQL Server

The locks in SQL Server fall into two categories:

• Shared locks
• Exclusive lock

Lock compatibility: The interplay of locks between transactions is known as lock compatibility.

Lock Mode	is it possible to hold an exclusive lock	whether a shared lock can be held
It has an exclusive lock.	Whether	Whether
Shared lock already held	Whether	Is

Resources that can be locked in SQL Server are: RIDs or keys (rows), pages, objects (such as tables), databases, and so on.

When you attempt to modify data (edit), the transaction requests an exclusive lock on the data resource regardless of the isolation level of the transaction. The lock is unlocked until the end of the transaction. For a single-statement transaction, the end of the statement is complete, and for a multi-statement transaction, execution of the commit tran or rollback TRAN command means the end of the transaction.

While a transaction holds an exclusive lock, other transactions cannot modify the data rows that the thing is manipulating, but can read the rows, depending on the isolation level of the transaction.

When an attempt is made to read data, the transaction defaults to the shared lock of the data resource, and the lock is released at the end of the transaction. The transaction isolation level allows you to control how locks are handled when a transaction reads data.

The transaction isolation level in SQL Server is divided into the following two main categories:

• Four isolation levels based on pessimistic concurrency control (the isolation level is enhanced from top to bottom):

  • READ UNCOMMITTED
  • READ COMMITTED (default)
  • Repeatable READ
  • SERIALIZABLE

• Two isolation levels based on optimistic concurrency control (the isolation level is enhanced from top to bottom):

  • SNAPSHOT
  • READ COMMITTED SNAPSHOT (default)

You can set the isolation level of a session by using the following statement:

SET TRANSACTION Isolation  Level < isolation name>

The isolation level determines the behavior of concurrent user reads or writes. During the duration of the lock and lock, it is not possible to control how the writer behaves, at which point the reader's behavior can be controlled. In addition, you can implicitly influence the behavior of the writer by controlling how the reader behaves. Higher isolation levels The higher the lock the reader requests, the longer the duration, the higher the consistency of the data, and the lower the concurrency.

READ COMMITTED SNAPSHOTand SNAPSHOT READ COMMITTED SERIALIZABLE The optimistic concurrency control implementations that can be seen as and correspond to.

When a transaction holds a lock on a data resource, if another transaction requests an incompatible lock for that resource, the request is blocked and goes into a wait state. The request waits until the locked resource is freed or waits for a timeout. You can query the transaction lock information in the database by using the following statement:

-- gets the current session ID SELECT @ @SPID ; -- querying the database for lock information SELECT *  from sys.dm_tran_locks; -- to turn off a session with ID 52 using the KILL command -- Note the KILL command is not SQL but a command that SQL Server uses to administer the database -- The kill command rolls back the transaction KILL ;

To set the lock time-out time, the lock timeout does not roll back the transaction:

-- Set lock timeout time is 5S SET  the ; -- Cancel time-out limit SET - 1;

READ Uncommitted

In this isolation level, the reader does not need to request a shared lock, and thus does not conflict with the writer who holds the exclusive lock. Thus, the reader can read the changes that the writer has not committed yet. That is, dirty reads.
In a query statement, READ COMMITTED you can shorthand for NOLOCK :

SELECT *  from  with (NOLOCK)

READ COMMITTED

At this isolation level, the reader must obtain a shared lock to prevent reading to uncommitted data. This means that if another transaction is modifying the resource, the reader must wait, and when the writer commits the transaction, the reader can obtain a shared lock for reading.

In this isolation level, the shared locks held by the firm do not persist until the end of the transaction, and when the query statement ends (or even does not end), the lock is released. This means that in the same thing, between two reads of the same data resource, there is no lock on the resource, so other transactions can modify the resource at two read intervals, resulting in inconsistencies in two read results, i.e. non-repeatable reads.

Repeatable READ

At this isolation level, the reader must acquire a shared lock and continue to the end of the transaction. The shared lock acquired by this isolation level locks only the resources that meet the query criteria when the query statement is executed. Examples are as follows:

SET TRANSACTION Isolation  Level Repeatable READ BEGIN TRAN SELECT *  from WHERE Id<ten;

The above statement will only lock the data rows that meet the ID<10 criteria, and if the ID<10 data in the table is id=2,3,4,5,6 Five, then only the five data will be locked:

--BlockingDELETE  fromAWHEREId=2;--does not blockDELETE  fromAWHEREId=7;--BlockingUPDATEASETName="' WHEREId=2;--does not blockUPDATEASETName="' WHEREId=7;--does not block, and the newly inserted data is not locked, you can perform update and delete operationsINSERT  intoA (Id,name)VALUES(7,'5');

This isolation level produces a phantom read, which means that new data is inserted between queries with the same transaction two times the same criteria, causing the second query to acquire new data.

SERIALIZABLE

At this isolation level, the reader must acquire a shared lock and continue to the end of the transaction. The shared lock at this isolation level not only locks the rows of data that meet the query criteria when the query statement is executed, but also locks the data rows that might be used in the future. That is, all actions that might affect the current read result are blocked.

Examples are as follows:

SET TRANSACTION Isolation  Level SERIALIZABLE BEGIN TRAN SELECT *  from WHERE Id<ten;

The above statement will only lock the data rows that meet the ID<10 criteria, if the ID<10 data in the table has id=2,3,4,5,6 five, then:

--BlockingDELETE  fromAWHEREId=2;--does not blockDELETE  fromAWHEREId=7;--BlockingUPDATEASETName="' WHEREId=2;--does not blockUPDATEASETName="' WHEREId=7;--blocking, this is not the same as repeatable READINSERT  intoA (Id,name)VALUES(7,'5');

SNAPSHOTAnd REAED COMMITTED SNPSHOT is the isolation level of SQL Server-based row versioning technology, where the reader does not acquire a shared lock at these two isolation levels. SQL Server can store the previous version of the committed row in the tempdb library. If the current version is not the version expected by the reader, SQL Server provides an older version.

SNAPSHOTLogically similar to SERIALIZABLE , logically similar to READ COMMITTED SNPSHOT READ COMMITTED . These two isolation levels are executed DELETE and the UPDATE statement needs to copy the version of the row, which is INSERT not required by the statement. As a result, the performance of the update and delete operations can have a negative impact, and the reader's performance is generally improved because there is no need to acquire a shared lock.

SNAPSHOT

At this isolation level, when the reader reads the data, it is to ensure that the last available row version is committed when the transaction starts. This means that the guaranteed fetch is read after the commit and can be read repeatedly, as well as ensuring that no phantom reads are obtained, as if it were in the SERIALIZABLE same level. However, the isolation level does not acquire a shared lock.

Enabling this isolation level requires that you first execute the following statement:

-- the snapshot-based isolation level needs to be enabled at the database level ALTER DATABASE SET on;  

-- Modify data does not commit transactions BEGIN TRAN  UPDATE SET Name='all'WHERE Id=2;

SET TRANSACTION Isolation  Level SNAPSHOT; -- queries are not blocked -- returns the old version available before the above transaction is committed, and returns the modified result after submission SELECT *  from XFH. [Table] WHERE Id=2;

Conflict Detection

In this isolation level of transactions, SQL Server does conflict detection to prevent update conflicts, where detection does not cause deadlock problems. That is, if a transaction at that isolation level modifies data, it will raise the following error if it finds that another transaction has modified the data of the same version number:

3960  - 2 A 4 -row Snapshot isolation transaction was aborted because of an update conflict. You cannot use snapshot isolation in database ' Test ' to directly or indirectly Access table ' A ' to update, delete, or insert rows that have been modified or deleted by another transaction. Please retry the transaction or change the isolation level of the Update/delete statement.

READ COMMITTED snpshot

The isolation level SNAPSHOT differs from the last committed row version that is available when the statement starts (not when the transaction starts).

Enabling this isolation level requires that you first execute the following statement:

-- the snapshot-based isolation level needs to be enabled at the database level -- to ensure that the link executing the statement must be a unique link to the target database ALTER DATABASE SET on;

Isolation level	allow dirty reads?	allow non-repeatable reads?	allow missing updates?	allow Phantom reading?	detecting update conflicts?	use row versioning?
READ uncommitted	Yes	Yes	Yes	Yes	no	no
READ COMMITTED	no	Yes	Yes	Yes	no	no
repeatable READ	no	no	no	Yes	no	no
SERIALIZABLE	no	no	no	no	no	no
SNAPSHOT	no	no	no	no	Yes	Yes
READ COMMITTED SNAPSHOT	no	Yes	Yes	Yes	no	yes

dead Lock

For deadlocks, SQL Server cleans itself. By default, SQL Server chooses to terminate a low-effort transaction to unlock the deadlock because the workload is less convenient for transaction rollback operations. The user can also set the deadlock priority DEADLOCK_PRIORITY so that the lower priority is terminated, regardless of the amount of work.

Conclusion

SQL Server provides four transaction isolation levels that do not depend on row versioning, and two transaction isolation levels that rely on row versioning. The isolation level of different transactions can have a greater effect on the execution of a data query statement (whether to acquire a shared lock, whether the statement will be blocked) and the result (whether it has dirty reads, Phantom reads, and so on), and the effect on modifying the data behavior is limited to whether the execution of the statement is blocked, because the statement that modifies the data must

The above is my own "SQL Server2012 T-code Basic Tutorial" Transaction and concurrent processing chapter of the reading notes, the wrong place to look at you more advice.

Bibliography Recommendations

Lock-to-transaction isolation level in SQL Server