A lock associates a database manager resource with an application to control how other applications can access the same resource. DB2® Everyplace® supports table locking
. That is, you either lock the entire table or you don't lock it at all. You cannot lock specific rows in a table.
DB2 Everyplace supports two types of table locks:
- Exclusive locks, used on DDL and DML statements.
- Shared locks, used on SELECT statements.
The following table shows how these lock types can be combined when multiple users or transactions access a table.
Table 1. Lock compatibility
An application can lock a table by calling the SQL statement LOCK TABLE. For example the following code obtains an exclusive lock on the table EMP.
LOCK TABLE EMP IN EXCLUSIVE MODE
Table locking is appropriate for read-only transactions and single-user access. When two or more transactions are updating the same table, table locking can lead to deadlock. For example, consider this scenario:
- Two transactions, A and B, obtain a shared lock on table T.
- Later, both transactions need to write to table T, which requires an exclusive lock.
- Neither transaction can obtain an exclusive lock, because the other transaction has a shared lock, and shared locks and exclusive locks are incompatible.
- Each transaction waits for the other to release the shared lock, resulting in a deadlock.
DB2 Everyplace provides a timeout mechanism that applications can use to resolve deadlocks. If an application cannot obtain a lock within a specified amount of time, the database engine rolls back the transaction and returns SQLSTATE 40001. The default lock timeout is 20 seconds.
Parent topic: Tuning database applications: XPD622