The type of lock that a standard table obtains depends
on the isolation level that is in effect and on the data access plan
that is being used.
The following tables show the types of locks that are
obtained for standard tables under each isolation level for different
access plans. Each entry has two parts: the table lock and the row
lock. A hyphen indicates that a particular lock granularity is not
available.
Tables 7-12 show the types of locks that are obtained
when the reading of data pages is deferred to allow the list of rows
to be further qualified using multiple indexes, or sorted for efficient
prefetching.
Note:
- Block-level locks are also available
for multidimensional clustering (MDC) and insert time clustering (ITC)
tables.
- Lock modes can be changed explicitly with the lock-request-clause of a SELECT statement.
Table 1. Lock Modes for Table
Scans with No Predicates
Isolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/- |
U/- |
SIX/X |
X/- |
X/- |
RS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/X |
IX/X |
Table 2. Lock Modes for Table
Scans with Predicates
Isolation level |
Read-only
and ambiguous scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/- |
U/- |
SIX/X |
U/- |
SIX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/U |
IX/X |
Note: Under the UR isolation
level, if there are predicates on include columns in the index, the
isolation level is upgraded to CS and the locks are upgraded to an
IS table lock or NS row locks.
|
Table 3. Lock Modes for RID
Index Scans with No Predicates
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/- |
IX/S |
IX/X |
X/- |
X/- |
RS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/X |
IX/X |
Table 4. Lock Modes for RID
Index Scans with a Single Qualifying Row
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/U |
IX/X |
IX/X |
IX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/X |
IX/X |
Table 5. Lock Modes for RID
Index Scans with Start and Stop Predicates Only
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/S |
IX/X |
IX/X |
IX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/X |
IX/X |
Table 6. Lock Modes for RID
Index Scans with Index and Other Predicates (sargs, resids) Only
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/S |
IX/X |
IX/S |
IX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/U |
IX/X |
Table 7. Lock Modes for Index
Scans Used for Deferred Data Page Access: RID Index Scan with No Predicates
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/S |
|
X/- |
|
RS |
IN/- |
IN/- |
|
IN/- |
|
CS |
IN/- |
IN/- |
|
IN/- |
|
UR |
IN/- |
IN/- |
|
IN/- |
|
Table 8. Lock Modes for Index
Scans Used for Deferred Data Page Access: After a RID Index Scan with
No Predicates
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/- |
IX/S |
IX/X |
X/- |
X/- |
RS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/X |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/X |
IX/X |
Table 9. Lock Modes for Index
Scans Used for Deferred Data Page Access: RID Index Scan with Predicates
(sargs, resids)
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/S |
|
IX/S |
|
RS |
IN/- |
IN/- |
|
IN/- |
|
CS |
IN/- |
IN/- |
|
IN/- |
|
UR |
IN/- |
IN/- |
|
IN/- |
|
Table 10. Lock Modes for Index
Scans Used for Deferred Data Page Access: After a RID Index Scan with
Predicates (sargs, resids)
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/- |
IX/S |
IX/X |
IX/S |
IX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
UR |
IN/- |
IX/U |
IX/X |
IX/U |
IX/X |
Table 11. Lock Modes for Index
Scans Used for Deferred Data Page Access: RID Index Scan with Start
and Stop Predicates Only
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S |
IX/S |
|
IX/X |
|
RS |
IN/- |
IN/- |
|
IN/- |
|
CS |
IN/- |
IN/- |
|
IN/- |
|
UR |
IN/- |
IN/- |
|
IN/- |
|
Table 12. Lock Modes for Index
Scans Used for Deferred Data Page Access: After a RID Index Scan with
Start and Stop Predicates Only
Isolation level |
Read-only and ambiguous
scans |
Cursored operations |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/- |
IX/S |
IX/X |
IX/X |
IX/X |
RS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
CS |
IS/NS |
IX/U |
IX/X |
IX/U |
IX/X |
UR |
IS/- |
IX/U |
IX/X |
IX/U |
IX/X |