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; BEGIN postgres=# SELECT * FROM test_tx_level WHERE val = 1 FOR SHARE; val ----- 1 (1 row)
T2		postgres=# begin; BEGIN postgres=# 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 detected DETAIL: Process 3368 waits for ExclusiveLock on tuple (0,1) of relation 139530 of database 13014; blocked by process 3380. Process 3380 waits for ShareLock on transaction 121862; blocked by process 3368. HINT: See server log for query details.
T5	UPDATE 1

To avoid deadlocks, one should also ensure that the first lock acquired in a transaction is the most restrictive mode. For example, if session 1 has used FOR UPDATE, then session 2 will be blocked instead of failing due to deadlocks.

As mentioned earlier, to avoid deadlocks, one can use a more restrictive mode. This forces other transactions to wait until the lock is removed. If the locking transaction is kept open for a long period, that means the application will suffer long delay.

Table of Contents for Deadlocks

Create new playlist

Sign In

Sign Up

Table of Contents for
Deadlocks