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.
Transaction Number |
Request Number |
SQL Text |
1 |
1 |
LOCKING table_1 FOR READ
CREATE INDEX (column_3, column_4) ON table_1;
|
2 |
2 |
BEGIN TRANSACTION;
|
3 |
SELECT *
FROM table_1;
|
|
4 |
UPDATE table_1
SET column_1 = <value-1>
WHERE column_1 = <value-2>;
|
|
5 |
END TRANSACTION;
|
Assume the actions in a transaction are taken in the following order.
1 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).
2 Request 3 places an READ lock on table_1.
3 Transaction 1 and request 3 can run concurrently when granted access to table_1.
4 Request 3 finishes but does not release the READ lock on table_1 until the end of the transaction.
5 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.
6 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.
7 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.
To avoid this deadlock, change your SQL in either of the following ways.
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;
CREATE INDEX (column_3, column_4) ON table_1;