Modes of transaction locks for various processes
Db2 uses different lock modes for different types of processes.
The rows in the following table show a sample of several types of Db2 processes. The columns show the most restrictive mode of locks used for different objects and the possible conflicts between application processes.
Process | Catalog table spaces | Skeleton tables (SKCT and SKPT) | Database descriptor (DBD)1 | Target table space 2 |
---|---|---|---|---|
Transaction with static SQL | IS 3 | S | n/a 4,5 | Any 6 |
Query with dynamic SQL | IS 7 | S | S8 | Any 6 |
BIND process | IX | X | S | n/a |
CREATE TABLE statement | IX | n/a | X | X9 |
ALTER TABLE statement | IX | X 10 | X | n/a |
ALTER TABLESPACE statement | IX | X 11 | X | n/a |
DROP TABLESPACE statement | IX | X 12 | X | n/a |
CREATE INDEX statement | IX | X13 | X |
S for DEFER NO
IS for DEFER YES |
DROP INDEX statement | IX | X13 | X | No lock on table space |
GRANT statement | IX | n/a | n/a | n/a |
REVOKE statement | IX | X 12 | n/a | n/a |
Notes:
- In a lock trace, these locks usually appear as locks on the DBD.
- The target table space is one of the following table spaces:
- Accessed and locked by an application process
- Processed by a utility
- Designated in the data definition statement
- The lock is held briefly to check EXECUTE authority.
- If the required DBD is not already in the EDM DBD cache, locks are acquired on table space DBD01, which effectively locks the DBD.
- When referential integrity is involved, an S-lock is held on the DBD to ensure serialization.
- For detailed information, see Locks acquired for SQL statements.
- Except while checking EXECUTE authority, IS locks on catalog tables are held until a commit point.
- If caching of dynamic SQL is turned on, no lock is taken on the DBD when a statement is prepared for insertion in the cache or for a statement in the cache.
- For segmented table spaces that are not partitioned, an X-lock is acquired on the table space. For universal table spaces, a lock is acquired on partition 1 of the table space.
- The package that uses the SKCT or SKPT is marked invalid if a referential constraint (such as a new primary key or foreign key) is added or changed, or the AUDIT attribute is added or changed for a table.
- These locks are not held when ALTER TABLESPACE is changing the following options: PRIQTY, SECQTY, PCTFREE, FREEPAGE, CLOSE, and ERASE.
- The package using the SKCT or SKPT is marked invalid as a result of this operation.
- An X-lock is acquired on the SKPT for packages that are dependent on the table on which the created or dropped index is defined.