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)
FROM
MyTable
WHERE
[fk_related_id][email protected]
IF (@findCount > 0)
BEGIN
ROLLBACK
TRANSACTION
RETURN
ERROR_CODE
END
INSERT
INTO
MyTable ([fk_related_id],…)
VALUES
(@Argument,…)
COMMIT
TRANSACTION
RETURN 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