Planned lessonMID-015-L05

Deadlocks

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

understand, diagnose, and prevent deadlocks.

Deadlock: T1 holds A and wants B, T2 holds B and wants A. PostgreSQL detects it after deadlock_timeout (default 1 s) and aborts one transaction with SQLSTATE 40P01.
Prevention: lock in a consistent order (sort ids), keep transactions short, avoid user interaction inside them.
Diagnose from the server log (it names the processes and queries) and from pg_locks joined with pg_stat_activity.
The aborted transaction can be retried.

Draft outline details

sql

SELECT a.pid, a.query, l.locktype, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE NOT l.granted;

What is the simplest rule to prevent most deadlocks?

Show answer

Always lock rows in the same order.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.