Example of a Transaction With Deadlock - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
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:

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;

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;