16.10 - Example of a Transaction Without Deadlock - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

This example demonstrates how to optimize concurrency.

Table Definition for the Example

Assume the following table definition:

     CREATE TABLE table_1 (
       column_1 INTEGER,
       column_2 INTEGER)
     PRIMARY INDEX (column_1);

Problem Transactions for the Example

Consider the following concurrently running transactions:

Transaction Number SQL Text
             1
LOCKING table_1 FOR READ
ALTER TABLE table_1, FALLBACK;
             2
SELECT *
FROM table_1;

Transaction Processing Without Deadlock

Assume these steps are taken in the following order.

  1. Transaction 1 places a table-level READ lock on table_1.
  2. Transaction 2 also places an table-level READ lock on table _1.
  3. Both transactions access table_1 at the same time and run concurrently.
  4. Transaction 1 builds the fallback concurrently with the SELECT request in the transaction 2, but does not complete until after transaction 2 completes and releases its lock on table_1. Then transaction 1 can upgrade its lock to EXCLUSIVE to complete the ALTER.