The type of lock that a multidimensional clustering (MDC) 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 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 |