Lock examples
Automatic locks
In this example, two sessions (A and B) attempt to perform operations on table T1. These operations automatically acquire the necessary locks.
At the beginning of the example, table T1 has one column (C1) and no rows.
The steps here represent a possible series of transactions from sessions A and B:
-
Transactions in both sessions acquire S locks to read from Table T1.
-
Session B releases its S lock with the COMMIT statement.
-
Session A can upgrade to an SI lock and insert into T1 because Session B released its S lock.
-
Session A releases its SI lock with a COMMIT statement.
-
Both sessions can acquire S locks because Session A released its SI lock.
-
Session A cannot acquire an SI lock because Session B has not released its S lock. SI locks are incompatible with S locks.
-
Session B releases its S lock with the COMMIT statement.
-
Session A can now upgrade to an SI lock and insert into T1.
-
Session B attempts to delete a row from T1 but can't acquire an X lock because Session A has not released its SI lock. SI locks are incompatible with X locks.
-
Session A continues to insert into T1.
-
Session A releases its SI lock.
-
Session B can now acquire an X lock and perform the delete.
This figure illustrates the previous steps:
Manual locks
In this example, Alice attempts to manually lock table customer_info with LOCK TABLE while Bob runs an INSERT statement:
Bob runs the following INSERT statement to acquire an INSERT lock and insert a row:
=> INSERT INTO customer_info VALUES(37189,'Albert','Quinlan','Frankfurt',2022);
In another session, Alice attempts to acquire a SHARE lock with LOCK TABLE. As shown in the lock compatibility table, the INSERT lock is incompatible with SHARE locks (among others), so Alice cannot acquire a SHARE lock until Bob finishes his transaction:
=> LOCK customer_info IN SHARE MODE NOWAIT;
ERROR 5157: Unavailable: [Txn 0xa00000001c48e3] S lock table - timeout error Timed out S locking Table:public.customer_info. I held by [user Bob (LOCK TABLE)]. Your current transaction isolation level is READ COMMITTED
Bob then releases the lock by calling COMMIT:
=> COMMIT;
COMMIT
Alice can now acquire the SHARE lock:
=> LOCK customer_info IN SHARE MODE NOWAIT;
LOCK TABLE
Bob tries to insert another row into the table, but because Alice has the SHARE lock, the statement enters a queue and appears to hang; after Alice finishes her transaction, the INSERT statement will automatically acquire the INSERT lock:
=> INSERT INTO customer_info VALUES(17441,'Kara','Shen','Cairo',2022);
Alice calls COMMIT, ending her transaction and releasing the SHARE lock:
=> COMMIT;
COMMIT;
Bob's INSERT statement automatically acquires the lock and completes the operation:
=> INSERT INTO customer_info VALUES(17441,'Kara','Shen','Cairo',2022);
OUTPUT
--------
1
(1 row)
Bob calls COMMIT, ending his transaction and releasing the INSERT lock:
=> COMMIT;
COMMIT