DB2 Version 9.7 for Linux, UNIX, and Windows

Lock modes for MDC block index scans

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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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
Isolation level	Read-only and ambiguous scans	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