Observing deadlocks and similar issues

Deadlocks are an important issue and can happen in every database. 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 following example shows what can happen:

 

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

psql: ERROR: deadlock detected
DETAIL: Process 91521 waits for ShareLock on transaction 903; 
   blocked by process 77185.
Process 77185 waits for ShareLock on transaction 905;
blocked by process 91521.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "t_deadlock"

PostgreSQL is even kind enough to tell us which row has caused the conflict. In my example, the root of all evil is a tuple, (0, 1). What you can see here is ctid, which is a unique identifier of a row in a table. It tells us about the physical position of a row inside the table. In this example, it is the first row in the first block (0).

It is even possible to query this row if it is still visible to your transaction. Here's how it works:

test=# SELECT ctid, * FROM t_deadlock WHERE ctid = '(0,  3)'; 
ctid   | id
-------+----- 
(0,1)  |  10
(1 row)

Keep in mind that this query might not return a row if it has already been deleted or modified.

However, this isn't the only case where deadlocks can lead to potentially failing transactions. Transactions can also not be serialized for various reasons. The following example shows what can happen. To make this example work, I assume that you've still got the two rows, id = 1 and id = 2:

 

In this example, two concurrent transactions are at work. As long as the first transaction is just selecting data, everything is fine because PostgreSQL can easily preserve the illusion of static data. But what happens if the second transaction commits a DELETE command? As long as there are only reads, there is still no problem. The trouble begins when the first transaction tries to delete or modify data, which is already dead at this point. The only solution for PostgreSQL is to error out due to a conflict caused by our transactions:

test=# DELETE FROM t_deadlock; 
psql: ERROR: could not serialize access due to concurrent update

Practically, this means that end users have to be prepared to handle erroneous transactions. If something goes wrong, properly written applications must be able to try again.