17.10 - Example of a Transaction Without Deadlock - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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.