15.10 - Example of a Transaction Without 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

This example demonstrates how to optimize concurrency.

Assume the following table definition.

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

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;

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.