Lock modes for MDC and ITC tables and RID index scans

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 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/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 Only
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/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 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/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 Row
Isolation 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 Only
Isolation 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 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/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 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/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 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 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 Only
Isolation 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 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/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