Lock types and duration during query processing

The types and the duration of locks acquired during query processing depend on the type of command, the locking scheme of the table, and the isolation level at which the command is run.

The lock duration depends on the isolation level and the type of query. Lock duration can be one of the following:

Scan duration – Locks are released when the scan moves off the row or page, for row or page locks, or when the scan of the table completes, for table locks.
Statement duration – Locks are released when the statement execution completes.
Transaction duration – Locks are released when the transaction completes.

Table 10-12 shows the types of locks acquired by queries at different isolation levels, for each locking scheme for queries that do not use cursors. Table 10-13 shows information for cursor-based queries.

**Table 10-12: Lock type and duration without cursors**
Statement	Isolation Level	Locking Scheme	Table Lock	Data Page Lock	Index Page Lock	Data Row Lock	Duration
Key: IS intent shared, IX intent exclusive, S shared, U update, X exclusive
select readtext any type of scan	0	allpages datapages datarows	- - -	- - -	- - -	- - -	No locks are acquired.
	1 2 with noholdlock 3 with noholdlock	allpages datapages datarows	IS IS IS	S * -	S - -	- - *	* Depends on setting of read committed with lock. See “Locking for select queries at isolation Level 1”.
	2	allpages datapages datarows	IS IS IS	S S -	S - -	- - S	Locks are released at the end of the transaction. See “Isolation Level 2 and Allpages-Locked tables”.
select via index scan	3 1 with holdlock 2 with holdlock	allpages datapages datarows	IS IS IS	S S -	S - -	- - S	Locks are released at the end of the transaction.
select via table scan	3 1 with holdlock 2 with holdlock	allpages datapages datarows	IS S S	S - -	- - -	- - -	Locks are released at the end of the transaction.
insert	0, 1, 2, 3	allpages datapages datarows	IX IX IX	X X -	X - -	- - X	Locks are released at the end of the transaction.
writetext	0, 1, 2, 3	allpages datapages datarows	IX IX IX	X X -	- - -	- - X	Locks are held on first text page or row; locks released at the end of the transaction.
delete update any type of scan	0, 1, 2	allpages datapages datarows	IX IX IX	U, X U, X -	U, X - -	- - U, X	“U” locks are released after the statement completes. “IX” and “X” locks are released at the end of the transaction.
delete update via index scan	3	allpages datapages datarows	IX IX IX	U, X U, X -	U, X - -	- - U, X	“U” locks are released after the statement completes. “IX” and “X” locks are released at the end of the transaction.
delete update via table scan	3	allpages datapages datarows	IX X X	U, X - -	- - -	- - -	Locks are released at the end of the transaction.

**Table 10-13: Lock type and duration with cursors**
Statement	Isolation Level	Locking Scheme	Table Lock	Data Page Lock	Index Page Lock	Data Row Lock	Duration
Key: IS intent shared, IX intent exclusive, S shared, U update, X exclusive
select (without for clause) select... for read only	0	allpages datapages datarows	- - -	- - -	- - -	- - -	No locks are acquired.
	1 2 with noholdlock 3 with noholdlock	allpages datapages datarows	IS IS IS	S * -	S - -	- - *	* Depends on setting of read committed with lock. See “Locking for select queries at isolation Level 1”.
	2, 3 1 with holdlock 2 with holdlock	allpages datapages datarows	IS IS IS	S S -	S - -	- - S	Locks become transactional after the cursor moves out of the page/row. Locks are released at the end of the transaction.
select...for update	1	allpages datapages datarows	IX IX IX	U, X U, X -	X - -	- - U, X	“U” locks are released after the cursor moves out of the page/row. “IX” and “X” locks are released at the end of the transaction.
select...for update with shared	1	allpages datapages datarows	IX IX IX	S, X S, X -	X - -	- - S, X	“S” locks are released after the cursor moves out of page/row. “IX” and “X” locks are released at the end of the transaction.
select...for update	2, 3, 1 holdlock 2, holdlock	allpages datapages datarows	IX IX IX	U, X U, X -	X - -	- - U, X	Locks become transactional after the cursor moves out of the page/row. Locks are released at the end of the transaction.
select...for update with shared	2, 3 1 with holdlock 2 with holdlock	allpages datapages datarows	IX IX IX	S, X S, X -	X - -	- - S, X	Locks become transactional after the cursor moves out of the page/row. Locks are released at the end of the transaction.