15.10 - Example of a Transaction With Deadlock - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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.

  • 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;