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|
LOCKING table_1 FOR READ ALTER TABLE table_1, FALLBACK;
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.