DB2 10.5 for Linux, UNIX, and Windows

Lock modes and access plans for standard tables

The type of lock that a standard table obtains 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 standard tables under each isolation level for different access plans. Each entry has two parts: the table lock and the row lock. A hyphen indicates that a particular lock granularity is not available.

Tables 7-12 show the types of locks that are obtained when the reading of data pages is deferred to allow the list of rows to be further qualified using multiple indexes, or sorted for efficient prefetching.

Note:

Block-level locks are also available for multidimensional clustering (MDC) and insert time clustering (ITC) tables.
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
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	S/-	U/-	SIX/X	X/-	X/-
RS	IS/NS	IX/U	IX/X	IX/X	IX/X
CS	IS/NS	IX/U	IX/X	IX/X	IX/X
UR	IN/-	IX/U	IX/X	IX/X	IX/X

Table 2. Lock Modes for Table Scans with Predicates
Isolation level	Read-only and ambiguous scans	Cursored operation		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	S/-	U/-	SIX/X	U/-	SIX/X
RS	IS/NS	IX/U	IX/X	IX/U	IX/X
CS	IS/NS	IX/U	IX/X	IX/U	IX/X
UR	IN/-	IX/U	IX/X	IX/U	IX/X
Note: 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 or NS row locks.

Table 3. Lock Modes for RID Index Scans with No Predicates
Isolation level	Read-only and ambiguous scans	Cursored operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	S/-	IX/S	IX/X	X/-	X/-
RS	IS/NS	IX/U	IX/X	IX/X	IX/X
CS	IS/NS	IX/U	IX/X	IX/X	IX/X
UR	IN/-	IX/U	IX/X	IX/X	IX/X

Table 4. Lock Modes for RID Index Scans with a Single Qualifying Row
Isolation level	Read-only and ambiguous scans	Cursored operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/U	IX/X	IX/X	IX/X
RS	IS/NS	IX/U	IX/X	IX/X	IX/X
CS	IS/NS	IX/U	IX/X	IX/X	IX/X
UR	IN/-	IX/U	IX/X	IX/X	IX/X

Table 5. Lock Modes for RID Index Scans with Start and Stop Predicates Only
Isolation level	Read-only and ambiguous scans	Cursored operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/S	IX/X	IX/X	IX/X
RS	IS/NS	IX/U	IX/X	IX/X	IX/X
CS	IS/NS	IX/U	IX/X	IX/X	IX/X
UR	IN/-	IX/U	IX/X	IX/X	IX/X

Table 6. Lock Modes for RID Index Scans with Index and Other Predicates (sargs, resids) Only
Isolation level	Read-only and ambiguous scans	Cursored operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/S	IX/X	IX/S	IX/X
RS	IS/NS	IX/U	IX/X	IX/U	IX/X
CS	IS/NS	IX/U	IX/X	IX/U	IX/X
UR	IN/-	IX/U	IX/X	IX/U	IX/X

Table 7. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/S		X/-
RS	IN/-	IN/-		IN/-
CS	IN/-	IN/-		IN/-
UR	IN/-	IN/-		IN/-

Table 8. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IN/-	IX/S	IX/X	X/-	X/-
RS	IS/NS	IX/U	IX/X	IX/X	IX/X
CS	IS/NS	IX/U	IX/X	IX/X	IX/X
UR	IN/-	IX/U	IX/X	IX/X	IX/X

Table 9. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/S		IX/S
RS	IN/-	IN/-		IN/-
CS	IN/-	IN/-		IN/-
UR	IN/-	IN/-		IN/-

Table 10. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IN/-	IX/S	IX/X	IX/S	IX/X
RS	IS/NS	IX/U	IX/X	IX/U	IX/X
CS	IS/NS	IX/U	IX/X	IX/U	IX/X
UR	IN/-	IX/U	IX/X	IX/U	IX/X

Table 11. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IS/S	IX/S		IX/X
RS	IN/-	IN/-		IN/-
CS	IN/-	IN/-		IN/-
UR	IN/-	IN/-		IN/-

Table 12. 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 operations		Searched update or delete
Isolation level	Read-only and ambiguous scans	Scan	Where current of	Scan	Update or delete
RR	IN/-	IX/S	IX/X	IX/X	IX/X
RS	IS/NS	IX/U	IX/X	IX/U	IX/X
CS	IS/NS	IX/U	IX/X	IX/U	IX/X
UR	IS/-	IX/U	IX/X	IX/U	IX/X