SQL Server transactions and locks (1)

The database deadlock problem has recently been encountered during stress testing in the project. In short, the followingCodeA deadlock occurs at the serializable isolation level:

 
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

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 in the beginning.