Modes of transaction locks for various processes

Db2 uses different lock modes for different types of processes.

Begin program-specific programming interface information. 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.

Table 1. Modes of Db2 transaction locks
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 Start of changeEnd of change IX X 11 X n/a
DROP TABLESPACE statement IX X 12 X n/a
Start of changeCREATE INDEX statementEnd of change Start of changeIXEnd of change Start of changeX13End of change Start of changeXEnd of change Start of change
S for DEFER NO
IS for DEFER YES
End of change
Start of changeDROP INDEX statementEnd of change Start of changeIXEnd of change Start of changeX13End of change Start of changeXEnd of change Start of changeNo lock on table spaceEnd of change
GRANT statement IX n/a n/a n/a
REVOKE statement IX X 12 n/a n/a
Notes:
  1. In a lock trace, these locks usually appear as locks on the DBD.
  2. 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
  3. The lock is held briefly to check EXECUTE authority.
  4. If the required DBD is not already in the EDM DBD cache, locks are acquired on table space DBD01, which effectively locks the DBD.
  5. When referential integrity is involved, an S-lock is held on the DBD to ensure serialization.
  6. For detailed information, see Locks acquired for SQL statements.
  7. Except while checking EXECUTE authority, IS locks on catalog tables are held until a commit point.
  8. Start of changeIf 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.End of change
  9. 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.
  10. 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.
  11. These locks are not held when ALTER TABLESPACE is changing the following options: PRIQTY, SECQTY, PCTFREE, FREEPAGE, CLOSE, and ERASE.
  12. The package using the SKCT or SKPT is marked invalid as a result of this operation.
  13. 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.
End program-specific programming interface information.