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.