Learning PostgreSQL 11 - Third Edition

Deadlocks

Deadlocks occur when two or more transactions each hold locks that the other wants. Using explicit locking can increase deadlocks. To simulate a deadlocks scenario, let's use FOR SHARE row-level locking, as follows:

Session 1	Session 2
T1	postgres=# begin;BEGINpostgres=# SELECT * FROM test_tx_level WHERE val = 1 FOR SHARE; val ----- 1(1 row)
T2	postgres=# begin;BEGINpostgres=# SELECT * FROM test_tx_level WHERE val = 1 FOR SHARE; val ----- 1(1 row)
T3	postgres=# UPDATE test_tx_level SET val = 2 WHERE val=1;
T4	postgres=# UPDATE test_tx_level SET val = 2 WHERE val=1;ERROR: deadlock detectedDETAIL: Process 3368 waits for ExclusiveLock on tuple (0,1) of relation 139530 of database 13014; blocked by process 3380. ...