Objects that are subject to locks

Db2 uses locks on various types of user and system object types to control changes to data by concurrent operations.

Begin program-specific programming interface information.Db2 uses locks on the following types of objects:

User data in target tables
A target table is a table that is accessed specifically in an SQL statement, and especially one that the statement updates, either by name or through a view. Locks on those tables are the most common concern, and the ones over which you have most control.
User data in related tables
Operations that are subject to referential constraints can require locks on related tables. For example, if you delete from a parent table, Db2 might delete rows from the dependent table as well. In that case, Db2 locks data in the dependent table as well as in the parent table.

Similarly, operations on rows that contain LOB or XML values might require locks on the LOB or XML table space and possibly on LOB or XML values within that table space. For more information, see Locks for LOB data and Locks for XML data.

If your application uses triggers, any triggered SQL statements can cause additional locks to be acquired.

Indexes and data-only locking
Instead of acquiring locks on index pages, Db2 uses a technique called data-only locking to serialize changes. However, in data sharing environments, Db2 uses index page p-locks.

Index page latches are acquired to serialize changes within a page and guarantee that the page is physically consistent. Acquiring page latches ensures that transactions accessing the same index page concurrently do not see the page in a partially changed state.

The underlying data page or row locks are acquired to serialize the reading and updating of index entries to ensure the data is logically consistent, meaning that the data is committed and not subject to rollback or abort. The data locks can be held for a long duration such as until commit. However, the page latches are only held for a short duration while the transaction is accessing the page. Because the index pages are not locked, hot spot insert scenarios (which involve several transactions trying to insert different entries into the same index page at the same time) do not cause contention problems in the index.

A query that uses index-only access might lock the data page or row, and that lock can contend with other processes that lock the data. However, using lock avoidance techniques can reduce the contention.

Pseudo-deleted index entries and data-locking
When data rows are deleted, index entries are not physically deleted unless the delete operation has exclusive control over the index page set. These index entries are called pseudo-deleted index entries. Subsequent searches continue to access these pseudo-deleted entries, which can gradually degrade performance as more rows are deleted. These pseudo-deleted index entries can also result in timeouts and deadlocks for applications that insert data into tables with unique indexes. Pseudo-empty index pages are pages that contain only pseudo-deleted index entries. The REORG utility removes pseudo-deleted index entries and pseudo-empty index pages when you run it to reorganize the data.

Automated cleanup of pseudo-empty index pages and pseudo-deleted index entries is enabled for all indexes by default when the value of the INDEX_CLEANUP_THREADS subsystem parameter is set to a non-zero value. However, you can specify time windows to enable or disable the index cleanup for the entire subsystem, for indexes in specific databases, or for specific indexes.

Data-only locking for XML data
When Db2 searches using XML values (the first key values) in the XML index key entries, it does not acquire the index page latch and does not lock either the base table data pages or rows, or the XML table space. When the matched-value index key entries are found, the corresponding DOCID values (the second key value) are retrieved. The retrieved DOCID values are used to retrieve the base table RIDs using the DOCID index. The regular data-only locking technique is applied on the DOCID index page and base table data page or row.
Db2 catalog objects
SQL data definition statements, GRANT statements, and REVOKE statements require locks on the Db2 catalog. If different application processes are issuing these types of statements, catalog contention can occur.

The following situations can cause contention on the Db2 catalog:

  • CREATE and DROP statements for a table space or index that uses a storage group contend significantly with other such statements.
  • CREATE, ALTER, and DROP DATABASE, and GRANT and REVOKE database privileges all contend with each other and with any other function that requires a database privilege.
  • CREATE, ALTER, and DROP STOGROUP contend with any SQL statements that refer to a storage group and with extensions to table spaces and indexes that use a storage group.
  • GRANT and REVOKE for plan, package, system, or use privileges contend with other GRANT and REVOKE statements for the same type of privilege and with data definition statements that require the same type of privilege. End program-specific programming interface information.
Skeleton cursor tables (SKCT) for application plans and skeleton package tables (SKPT) for packages

The following operations require incompatible locks on the SKCT or SKPT, whichever is applicable, and cannot run concurrently:

  • Binding, rebinding, or freeing the plan or package
  • Dropping a resource or revoking a privilege that the plan or package depends on
  • In some cases, altering a resource that the plan or package depends on
Database descriptors (DBDs) that represent databases
If the DBD is not in the EDM DBD cache, most processes acquire locks on the database descriptor table space (DBD01), which has the effect of locking the DBD and can cause conflict with other processes. However, if he DBD is in the EDM DBD cache, the lock on the DBD depends on the type of process, as shown in the following table:
Table 1. Contention for locks on a DBD in the EDM DBD cache
Process Type Process Lock acquired Conflicts with process type
1 Static SQL data manipulation statements (such as SELECT, INSERT, UPDATE, DELETE)1 none3 none3
2 Dynamic SQL data manipulation SQL statements2 S 3
3 Data definition statements (ALTER, CREATE, DROP) X 2,3,4
4 Utilities S4 3
Notes:
  1. Static SQL statements can conflict with other processes because of locks on data.
  2. 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.
  3. When referential integrity is involved, S-locks are held on the DBD to ensure serialization. These locks can conflict with data definition statements.
  4. Start of changeREORG and LOAD utilities might acquire X-locks on the DBD during the SWITCH phase.End of change
End program-specific programming interface information.