SQL Transactions and Locks

Excerpt from: http://www.cnblogs.com/lxconan/archive/2011/10/20/sql_transaction_n_locks_1.html

A recent stress test in a project has encountered a deadlock problem with the database, in short, the following code causes a deadlock at the SERIALIZABLE isolation level:

1234567891011121314

SELECT@findCount=COUNT(id) FROMMyTableWHERE[fk_related_id][email protected]IF (@findCount > 0)BEGIN    ROLLBACKTRANSACTION    RETURNERROR_CODEENDINSERTINTOMyTable ([fk_related_id],…)VALUES(@Argument,…)COMMITTRANSACTIONRETURN SUCCESS_CODE

In the process of figuring out the problem, I did a lot of experiments to share with you. This is the beginning, mainly on the four kinds of SQL Server (in fact, there are other) classic transaction isolation levels, and the use of locks at different isolation levels, and the resulting different data consistency.

Types of locks in SQL Server (schema operation is not involved for the time being)

Lock type	Describe
(Shared Lock)	For read-only operation data locking
(Update Lock)	For data updates, you will be requested to upgrade to an X lock when the data really needs to be updated.
X (Exclusive Lock)	An exclusive lock that is used for data changes.
Key-range Lock (discussed later)	Protect data only at the Serializable isolation level to avoid any data insertions that could cause the second read of this transaction to produce an error

Use of locks under individual transaction isolation levels

There are four types of transaction isolation levels in SQL Server, specifically for everyone to participate in MSDN. Below is a list of how these locks are used under different transaction isolation levels:

Isolation level	Read Data lock status	Write Data lock status	Lock holding Time
Read UNCOMMITTED	Do not get any locks	Do not get any locks
Read Committed	Data Acquisition S Lock	For INSERT, DELETE, update execution, get x lock, for update mark, get U lock;	After reading, the release is not held until the end of the transaction.
Repeatable Read	Data Acquisition S Lock	For INSERT, DELETE, update execution, get x lock, for update mark, get U lock;	Hold to end of transaction
Serializable	The data obtains the S lock while acquiring the Key-range lock.	For the execution of INSERT, DELETE, UPDATE, the x lock is obtained, the U lock is obtained for the update token, and the Key-range lock is obtained.	Hold to end of transaction

We can use this knowledge image to illustrate the consistency of data across isolation levels:

Read UNCOMMITTED level

(1) Dirty reading

(2) Missing updates

(3) Non-repeatable reading

(4) Phantom reading

Read Committed Level

(1) Dirty reading

(2) Missing updates

(3) Non-repeatable reading

(4) Phantom reading

Repeatable Read Level

(1) Dirty reading

(2) Missing updates

(3) Non-repeatable reading

(4) Phantom reading

Serializable level

(1) Dirty reading

(2) Missing updates

(3) Non-repeatable reading

(4) Phantom reading

We can see the following conclusions from a more intuitive

	Dirty Read	Update lost	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	possible	possible	possible	possible
Read Committed	No way	possible	possible	possible
Repeatable Read	No way	No way	No way	possible
Serializable	No way	No way	No way	No way

This concludes with a detailed introduction to Key-range lock and an analysis of the deadlock problem mentioned at the beginning of this article.

SQL Transactions and Locks