15.10 - Example of Two Serial Transactions - 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 shows 2 transactions. The first transaction begins its operations before the second transaction.

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, with the first having started prior to the second.

 

    Transaction

        Number

                                                             SQL Text

              1

LOCKING table_1 FOR READ 
CREATE INDEX (column_3, column_4) ON table_1;

             2

SELECT * 
FROM table_1 
WHERE column_3 = 124 
AND column_4 = 93;

Each transaction places a table-level READ lock on table_1. The transactions obtain access to table_1 and run concurrently.

Note: The SELECT request does not recognize the index being created by the CREATE INDEX request.

To eliminate concurrency, change the coding of transaction 1 to make its explicit lock EXCLUSIVE.

 

     Transaction

        Number

                                                                    SQL Text

             1

LOCKING table_1 FOR EXCLUSIVE 
CREATE INDEX (column_3, column_4) ON table_1;

             2

SELECT * 
FROM table_1 
WHERE column_3 = 124
AND column_4 = 93;

The upgraded LOCKING FOR EXCLUSIVE modifier in transaction 1 blocks the table‑level READ lock request on table_1 in transaction 2.