August 1, 2024
Summary: in this tutorial, you will learn how to check deadlocks in PostgreSQL.
Table of Contents
Introduction
Many people might have seen PostgreSQL issue an error message: "ERROR: deadlock detected". But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean.
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. Deadlocks are related to blocked queries, but slightly different, which result in a cancelled query due to it deadlocking against another query.
Example
Let’s look at a simple example, and start with a little test setup:
CREATE TABLE test1 (id int, num int);
INSERT INTO test1 VALUES (1, 100), (2, 200);
The easiest way to reproduce a deadlock is doing the following:
--- session 1
BEGIN;
UPDATE test1 SET num = num + 10 WHERE id = 1;
--- session 2
BEGIN;
UPDATE test1 SET num = num + 10 WHERE id = 2;
UPDATE test1 SET num = num + 10 WHERE id = 1; --- this will block waiting for session 1 to finish
--- session 1
UPDATE test1 SET num = num + 10 WHERE id = 2; --- this can never finish as it deadlocks against session 2
After deadlock_timeout seconds, you will see the locking problem in PostgreSQL logs. In this case it decides that this will never finish, and emit the following to the logs:
ERROR: deadlock detected
DETAIL: Process 70725 waits for ShareLock on transaction 891717; blocked by process 70713.
Process 70713 waits for ShareLock on transaction 891718; blocked by process 70725.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "test1"
PostgreSQL is even kind enough to tell us which row has caused the conflict. In this 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).
You might think that deadlocks never happen in production, but the unfortunate truth is that heavy use of ORM frameworks can hide the circular dependency situation that produces deadlocks, and its certainly something to watch out for when you make use of complex transactions.
Query the pg_stat_database view
You can see database-level statistics in the view pg_stat_database, which has a deadlocks column. You should pay attention to this column, because it will tell you whether your database has many deadlocks, which will inevitably slow down operations.
When two or more transactions have locked resources and are trying to obtain resources already locked by other transactions, a deadlock will happen. In normal situation xact A locks resource A and xact B locks resource B. In deadlock situation xact A will try to lock resource B. This, in turn puts it into hold mode since xact B has already locked resource B. This way both transactions lock each other’s resources. When a deadlock occurs, postgres cancels one of the involved transactions and removes it from the waiting queue so that other xacts continue their work.
As a rule, deadlocks warn you about application’s bad design, and general recommendation here is to fix application logic which causes the deadlocks. Good starting point here is to enable log_lock_waits option in postgresql.conf, reload and check logs time to time. When a deadlock occurs, extra information about conflicted queries will be logged.
We can check deadlocks using the following query:
SELECT deadlocks FROM pg_stat_database
WHERE datname = current_database();
Once you’ve found many deadlocks, you can locate the queries caused deadlocks to optimize them.