The type of lock that a multidimensional clustering (MDC)
table obtains during a block 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 5-12
show the types of locks that are obtained for block index scans when
the reading of data pages is deferred.
Note: Lock modes can be changed explicitly with the lock-request-clause of a SELECT statement.
Table 1. Lock Modes for
Index 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/--/-- |
IX/IX/S |
IX/IX/X |
X/--/-- |
X/--/-- |
| RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
| CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
| UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
Table 2. Lock Modes for
Index Scans with Predicates on Dimension Columns Only
| Isolation level |
Read-only and ambiguous
scans |
Cursored operation |
Searched update or delete |
| Scan |
Where current of |
Scan |
Update or delete |
| RR |
IS/-/- |
IX/IX/S |
IX/IX/X |
X/-/- |
X/-/- |
| RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/X/- |
IX/X/- |
| 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 3. Lock Modes for
Index Scans with Start and Stop Predicates Only
| 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 |
IX/IX/S |
IX/IX/S |
| RS |
IX/IX/S |
IX/IX/U |
IX/IX/X |
IX/IX/- |
IX/IX/- |
| CS |
IX/IX/S |
IX/IX/U |
IX/IX/X |
IX/IX/- |
IX/IX/- |
| UR |
IN/IN/- |
IX/IX/U |
IX/IX/X |
IX/IX/- |
IX/IX/- |
Table 4. Lock Modes for
Index 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 |
IS/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 5. Lock Modes for
Index Scans Used for Deferred Data Page Access: Block Index Scan 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 |
IS/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 6. Lock Modes for
Index Scans Used for Deferred Data Page Access: After a Block Index
Scan 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 |
IN/IN/-- |
IX/IX/S |
IX/IX/X |
X/--/-- |
X/--/-- |
| RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
| CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
| UR |
IN/IN/-- |
IX/IX/U |
IX/IX/X |
X/X/-- |
X/X/-- |
Table 7. Lock Modes for
Index Scans Used for Deferred Data Page Access: Block Index Scan with
Predicates on Dimension Columns Only
| 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/-- |
|
IX/S/-- |
|
| RS |
IS/IS/NS |
IX/--/-- |
|
IX/--/-- |
|
| CS |
IS/IS/NS |
IX/--/-- |
|
IX/--/-- |
|
| UR |
IN/IN/-- |
IX/--/-- |
|
IX/--/-- |
|
Table 8. Lock Modes for
Index Scans Used for Deferred Data Page Access: After a Block Index
Scan with Predicates on Dimension Columns Only
| 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/S/-- |
IX/X/-- |
| RS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/U/-- |
IX/X/-- |
| CS |
IS/IS/NS |
IX/IX/U |
IX/IX/X |
IX/U/-- |
IX/X/-- |
| UR |
IN/IN/-- |
IX/IX/U |
IX/IX/X |
IX/U/-- |
IX/X/-- |
Table 9. Lock Modes for
Index Scans Used for Deferred Data Page Access: Block Index Scan with
Start and Stop Predicates Only
| 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/-- |
|
IX/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 Block Index
Scan with Start and Stop Predicates Only
| 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/X |
|
IX/X/-- |
|
| RS |
IS/IS/NS |
IN/IN/-- |
|
IN/IN/-- |
|
| CS |
IS/IS/NS |
IN/IN/-- |
|
IN/IN/-- |
|
| UR |
IS/--/-- |
IN/IN/-- |
|
IN/IN/-- |
|
Table 11. Lock Modes for
Index Scans Used for Deferred Data Page Access: Block Index Scan 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/-- |
IX/IX/-- |
|
IX/IX/-- |
|
| 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 Block Index
Scan 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 |
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 |