Recently, the stress test in the project encountered a database deadlock problem. In short, the following code causes a deadlock at the SERIALIZABLE isolation level:
The code is as follows: |
Copy code |
SELECT @ findCount = COUNT (id) FROM MyTableWHERE [fk_related_id] = @ ArgumentIF (@ findCount> 0) beginrollback transactionreturn ERROR_CODEENDINSERT INTO MyTable ([fk_related_id],…) VALUES (@ Argument ,...) Commit transactionreturn SUCCESS_CODE |
I have done a lot of experiments while figuring out the problem and share it with you. This article is the beginning of this article. It mainly describes the four types of SQL Server (in fact, there are other) classic transaction isolation levels, as well as the use of locks at different isolation levels, and bring about different data consistency.
Types of locks in SQL Server (Schema operations are not involved at the moment)
Lock type |
Description |
(Shared Lock) |
Used to lock read-only operation data |
(Update Lock) |
Used for data update. When the data actually needs to be updated, it will apply for upgrading to the X lock. |
X (Exclusive Lock) |
Exclusive lock for data changes. |
Key-Range Lock (discussed later) |
Only protects data at the Serializable isolation level to avoid any data insertion operations that may cause the second read of information in this transaction to produce errors. |
Use of locks at the transaction isolation level
There are four transaction isolation levels in SQL Server. For details, refer to the MSDN. The following lists how these locks are used at different transaction isolation levels:
Isolation level |
Read data lock status |
Write Data lock status |
Lock hold time |
Read Uncommitted |
Do not obtain any lock |
Do not obtain any lock |
|
Read Committed |
Data acquisition S Lock |
For the execution of INSERT, DELETE, UPDATE, obtain the X lock; for the UPDATE tag, obtain the U lock; |
Once read, it is released, and it is not held until the transaction ends. |
Repeatable Read |
Data acquisition S Lock |
For the execution of INSERT, DELETE, UPDATE, obtain the X lock; for the UPDATE tag, obtain the U lock; |
Hold until the transaction ends |
Serializable |
The data acquires the S lock and the Key-Range lock. |
For the execution of INSERT, DELETE, and UPDATE, the X lock is obtained. For the UPDATE tag, the U lock is obtained and the Key-Range lock is also obtained. |
Hold until the transaction ends |
We can use this knowledge to demonstrate data consistency at different isolation levels:
Read Uncommitted level
(1) dirty read
(2) update loss
(3) non-repeated read
(4) Phantom read
Read Committed level
(1) dirty read
(2) update loss
(3) non-repeated read
(4) Phantom read
Repeatable Read level
(1) dirty read
(2) update loss
(3) non-repeated read
(4) Phantom read
Serializable level
(1) dirty read
(2) update loss
(3) non-repeated read
(4) Phantom read
From the figure above, we can intuitively see the following conclusions:
|
Dirty read |
Update loss |
Non-repeated read |
Phantom read |
Read Uncommitted |
Possible |
Possible |
Possible |
Possible |
Read Committed |
Impossible |
Possible |
Possible |
Possible |
Repeatable Read |
Impossible |
Impossible |
Impossible |
Possible |
Serializable |
Impossible |
Impossible |
Impossible |
Impossible |
So far, the next article will introduce Key-Range Lock in detail and analyze the deadlock problems mentioned at the beginning.