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);
Problem Transactions for the Example
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; |
Transaction Processing without Deadlock
Assume these steps are taken in the following order:
- Transaction 1 places a table-level READ lock on table_1.
- Transaction 2 also places an table-level READ lock on table _1.
- Both transactions access table_1 at the same time and run concurrently.
- 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.