The type of lock that a multidimensional clustering (MDC)
or insert time clustering (ITC) table obtains during a table or RID
index scan 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 MDC and ITC tables under each
isolation level for different access plans. Each entry has three parts:
the table lock, the block lock, and the row lock. A hyphen indicates
that a particular lock granularity is not available.
Tables
9-14 show the types of locks that are obtained for RID index scans
when the reading of data pages is deferred. 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, an IS block lock, or NS row locks.
Note: Lock modes can be changed explicitly with the lock-request-clause of a SELECT statement.
Table 1. Lock Modes for
Table Scans with No PredicatesIsolation level |
Read-only
and ambiguous scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/-/- |
U/-/- |
SIX/IX/X |
X/-/- |
X/-/- |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/U |
IX/X/- |
IX/I/- |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/X/- |
IX/X/- |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/X/- |
IX/X/- |
Table 2. Lock Modes for
Table Scans with Predicates on Dimension Columns OnlyIsolation level |
Read-only
and ambiguous scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/-/- |
U/-/- |
SIX/IX/X |
U/-/- |
SIX/X/- |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/U/- |
X/X/- |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/U/- |
X/X/- |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/U/- |
X/X/- |
Table 3. Lock Modes for
Table Scans with Other Predicates (sargs, resids)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/IX/X |
U/-/- |
SIX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
Table 4. Lock Modes for
RID Index Scans with No PredicatesIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
S/-/- |
IX/IX/S |
IX/IX/X |
X/-/- |
X/-/- |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
Table 5. Lock Modes for
RID Index Scans with a Single Qualifying RowIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/IS/S |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
X/X/X |
X/X/X |
Table 6. Lock Modes for
RID Index Scans with Start and Stop Predicates OnlyIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/IS/S |
IX/IX/S |
IX/IX/X |
IX/IX/X |
IX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
Table 7. Lock Modes for
RID Index Scans with Index Predicates OnlyIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S/S |
IX/IX/S |
IX/IX/X |
IX/IX/S |
IX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
Table 8. Lock Modes for
RID Index Scans with Other Predicates (sargs, resids)Isolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S/S |
IX/IX/S |
IX/IX/X |
IX/IX/S |
IX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
Table 9. Lock Modes for
Index Scans Used for Deferred Data Page Access: RID Index Scan with
No PredicatesIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S/S |
IX/IX/S |
|
X/-/- |
|
RS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
CS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
UR |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
Table 10. Lock Modes for
Index Scans Used for Deferred Data Page Access: After a RID Index
Scan with No PredicatesIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/IN/- |
IX/IX/S |
IX/IX/X |
X/-/- |
X/-/- |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/X |
IX/IX/X |
Table 11. 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 operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/S/- |
IX/IX/S |
|
IX/IX/S |
|
RS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
CS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
UR |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
Table 12. 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 operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/IN/- |
IX/IX/S |
IX/IX/X |
IX/IX/S |
IX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
Table 13. Lock Modes for
Index Scans Used for Deferred Data Page Access: RID Index Scan with
Start and Stop Predicates OnlyIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IS/IS/S |
IX/IX/S |
|
IX/IX/X |
|
RS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
CS |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
UR |
IN/IN/- |
IN/IN/- |
|
IN/IN/- |
|
Table 14. Lock Modes for
Index Scans Used for Deferred Data Page Access: After a RID Index
Scan with Start and Stop Predicates OnlyIsolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
Scan |
Where current of |
Scan |
Update or delete |
RR |
IN/IN/- |
IX/IX/S |
IX/IX/X |
IX/IX/X |
IX/IX/X |
RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |
UR |
IS/-/- |
IX/IX/U |
IX/IX/X |
IX/IX/U |
IX/IX/X |