Table Space Locks

A table space lock is acquired when a DB2 table or index is accessed. Note that I said accessed, not updated . The table space is locked even when simple read-only access is occurring.

Refer to Table 23.1 for a listing of the types of table space locks that can be acquired during the execution of an SQL statement. Every table space lock implies two types of access: the access acquired by the lock requester and the access allowed to other subsequent , concurrent processes.

Table 23.1. Table Space Locks

Lock	Meaning	Access Acquired	Access Allowed to Others
S	SHARE	Read only	Read only
U	UPDATE	Read with intent to update	Read only
X	EXCLUSIVE	Update	No access
IS	INTENT SHARE	Read only	Update
IX	INTENT EXCLUSIVE	Update	Update
SIX	SHARE/INTENT EXCLUSIVE	Read or Update	Read only

When an SQL statement is issued and first accesses data, it takes an intent lock on the table space. Later in the process, actual S -, U -, or X -locks are taken. The intent locks ( IS , IX , and SIX ) enable programs to wait for the required S -, U -, or X -lock that needs to be taken until other processes have released competing locks.

The type of table space lock used by DB2 during processing is contingent on several factors, including the table space LOCKSIZE specified in the DDL, the bind parameters chosen for the plan being run, and the type of processing requested . Table 23.2 provides a synopsis of the initial table space locks acquired under certain conditions.

Table 23.2. How Table Space Locks Are Acquired

Type of Processing	LOCKSIZE	Isolation	Initial Lock Acquired
MODIFY	ANY	CS	IX
MODIFY	PAGE/ROW	CS	IX
MODIFY	TABLESPACE	CS	X
MODIFY	ANY	RR	X
MODIFY	PAGE/ROW	RR	X
MODIFY	TABLESPACE	RR	X
SELECT	ANY	CS	IS
SELECT	PAGE/ROW	CS	IS
SELECT	TABLESPACE	CS	S
SELECT	ANY	RR	S
SELECT	PAGE/ROW	RR	S
SELECT	TABLESPACE	RR	S

A table space U -lock indicates intent to update, but an update has not occurred. This is caused by using a cursor with the FOR UPDATE OF clause. A U -lock is non-exclusive because it can be taken while tasks have S -locks on the same table space. More information on table space lock compatibility follows in Table 23.3.

An additional consideration is that table space locks are usually taken in combination with table and page locks, but they can be used on their own. When you specify the LOCKSIZE TABLESPACE DDL parameter, table space locks alone are used as the locking mechanism for the data in that table space. This way, concurrent access is limited and concurrent update processing is eliminated.

Similar in function to the LOCKSIZE DDL parameter is the LOCK TABLE statement. The LOCK TABLE statement requests an immediate lock on the specified table. The LOCK TABLE statement has two forms ”one to request a share lock and one to request an exclusive lock.

 LOCK TABLE  table_name  IN SHARE MODE; LOCK TABLE  table_name  IN EXCLUSIVE MODE; 

CAUTION

The LOCK TABLE statement locks all tables in a simple table space even though only one table is specified.

A locking scheme is not effective unless multiple processes can secure different types of locks on the same resource concurrently. With DB2 locking, some types of table space locks can be acquired concurrently by discrete processes. Two locks that can be acquired concurrently on the same resource are said to be compatible with one another.

Refer to Table 23.3 for a breakdown of DB2 table space lock compatibility. A Yes in the matrix indicates that the two locks are compatible and can be acquired by distinct processes on the same table space concurrently. A No indicates that the two locks are incompatible. In general, two locks cannot be taken concurrently if they allow concurrent processes to negatively affect the integrity of data in the table space.

Table 23.3. Table Space Lock Compatibility Matrix

Locks for PGM2	Locks for PGM1
	S	U	X	IS	IX	SIX
S	Yes	Yes	No	Yes	No	No
U	Yes	No	No	Yes	No	No
X	No	No	No	No	No	No
IS	Yes	Yes	No	Yes	Yes	Yes
IX	No	No	No	Yes	Yes	No
SIX	No	No	No	Yes	No	No