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:

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.