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 ...

Get Mastering PostgreSQL 10 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial