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

Table Definition

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

Problem Transactions

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.

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

Resolving the Problem

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.