Example of a Transaction with Deadlock - Analytics Database - Teradata Vantage

The following example demonstrates a deadlock situation.

Table Definition

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;

Problem Transactions

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.

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;