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

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

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.