15.10 - Example of a Transaction With Deadlock - Teradata Database

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;

BEGIN TRANSACTION;

SELECT * 

FROM table_1;

UPDATE table_1 

SET   column_1 = <value-1> 

WHERE column_1 = <value-2>;

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;