Lock modes for MDC and ITC tables and RID index scans

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.

• Table 1. Lock Modes for Table Scans with No Predicates
• Table 2. Lock Modes for Table Scans with Predicates on Dimension Columns Only
• Table 3. Lock Modes for Table Scans with Other Predicates (sargs, resids)
• Table 4. Lock Modes for RID Index Scans with No Predicates
• Table 5. Lock Modes for RID Index Scans with a Single Qualifying Row
• Table 6. Lock Modes for RID Index Scans with Start and Stop Predicates Only
• Table 7. Lock Modes for RID Index Scans with Index Predicates Only
• Table 8. Lock Modes for RID Index Scans with Other Predicates (sargs, resids)
• Table 9. Lock Modes for Index Scans Used for Deferred Data Page Access: RID Index Scan with No Predicates
• Table 10. Lock Modes for Index Scans Used for Deferred Data Page Access: After a RID Index Scan with No Predicates
• Table 11. Lock Modes for Index Scans Used for Deferred Data Page Access: RID Index Scan with Predicates (sargs, resids)
• Table 12. Lock Modes for Index Scans Used for Deferred Data Page Access: After a RID Index Scan with Predicates (sargs, resids)
• Table 13. Lock Modes for Index Scans Used for Deferred Data Page Access: RID Index Scan with Start and Stop Predicates Only
• Table 14. Lock Modes for Index Scans Used for Deferred Data Page Access: After a RID Index Scan with Start and Stop Predicates Only