The following example demonstrates a deadlock situation.
Consider the following table definition:
CREATE TABLE table_1 ( column_1 INTEGER, column_2 INTEGER, column_3 INTEGER, column_4 INTEGER) PRIMARY INDEX (column_1);
The following two transactions are running concurrently:
LOCKING table_1 FOR READ CREATE INDEX (column_3, column_4) ON table_1;
SELECT * FROM table_1;
UPDATE table_1 SET column_1 = <value-1> WHERE column_1 = <value-2>;
Assume the actions in a transaction are taken in the following order:
- Transaction 1 places a READ lock on table_1.
This lock is in effect until the table header needs to change (after the creation of the index subtables is complete).
- Request 3 places an READ lock on table_1.
- Transaction 1 and request 3 can run concurrently when granted access to table_1.
- Request 3 finishes but does not release the READ lock on table_1 until the end of the transaction.
- Request 4 attempts to place a WRITE rowhash-level lock on table_1.
Its lock request is blocked because of the READ lock placed on table_1 by transaction 1.
- Transaction 1 needs to upgrade its lock from READ to EXCLUSIVE.
Its lock request is blocked because of the WRITE rowhash-level lock placed by request 4 in transaction 2.
- At this point there is a deadlock situation: request 4 in transaction 2 is waiting for transaction 1 to release its lock, and transaction 1 is blocked by request 4.
Resolving the Problem
To avoid this deadlock, change your SQL in either of the following ways:
- Add the modifier LOCKING table_1 FOR WRITE to transaction 2 as follows.
LOCKING table_1 FOR WRITE BEGIN TRANSACTION; SELECT * FROM table_1; UPDATE table_1 SET column_1 = value_1 WHERE column_1 = value_2; END TRANSACTION;
- Remove the LOCKING request modifier from transaction 1.
CREATE INDEX (column_3, column_4) ON table_1;