Observing deadlocks and similar issues

Deadlocks are an important issue and can happen in every database I am aware of. Basically, a deadlock will happen if two transactions have to wait on each other.

In this section, you will see how this can happen. Let's suppose we have a table containing two rows:

CREATE TABLE t_deadlock (id int); 
INSERT INTO t_deadlock VALUES (1), (2);

The next listing shows what can happen:

Transaction 1	Transaction 2
BEGIN;	BEGIN;
UPDATE t_deadlock SET id = id * 10 WHERE id = 1;	UPDATE t_deadlock SET id = id * 10 WHERE id = 2;
UPDATE t_deadlock SET id = id * 10 WHERE id = 2;
Waiting on transaction 2	UPDATE t_deadlock SET id = id * 10 WHERE id = 1;
Waiting on transaction 2	Waiting on transaction 1
	Deadlock will be resolved after one second (deadlock_timeout)
COMMIT;	ROLLBACK;

 

As soon as the deadlock is detected, the following error message will show up:

ERROR: deadlock detected
DETAIL: Process 91521 waits for ShareLock on transaction 903; 
   blocked by process 77185.
Process 77185 waits for...