Demo of deadlocks with PostgreSQL

deadlock_demo.md

Deadlocks Demo

For a basic discussion and demo, see https://www.postgresql.org/docs/current/explicit-locking.html#id-1.5.12.6.8.2

My own demo follows.

Setup

Create two tables with unique indexes:

CREATE TABLE users (name text, email text);
CREATE UNIQUE INDEX users_email ON users (email);

CREATE TABLE products (name text, price integer);
CREATE UNIQUE INDEX products_name ON products (name);

Create a Deadlock

Start two psql sessions.

In session 1, start a transaction:

BEGIN;

Then insert a record:

INSERT INTO users (name, email)
VALUES
('mo', 'mo@example.com');

That insert succeeds. The transaction is not yet committed, but it has a "lock" on inserting the record with this email.

Leaving that transaction open, in session 2, start a transaction:

BEGIN;

Then insert a record into the other table.

INSERT INTO products (name, price)
VALUES
('pickle juicer', 99);

That insert succeeds, too. Again, the transaction is not yet committed, but it has a "lock" on inserting the product with this name.

Back in session 1, try to insert the same product:

INSERT INTO products (name, price)
VALUES
('pickle juicer', 99);

That insert blocks. Because session 2 already has a lock on inserting that product, session 1 does not yet know whether it can perform this insert. If transaction 2 ends with ROLLBACK, transaction 1 will be able to perform this insert. But if transaction 2 ends with COMMIT, transaction 1 will be get a unique constraint violation.

You can try both of those scenarios later. For now, let's make the problem worse. Transaction 1 is blocked to see what happens with transaction 2; now let's make transaction 2 block waiting to see what happens with transaction 1.

In session 2, try to insert the user:

INSERT INTO users (name, email)
VALUES
('mo', 'mo@example.com');

At this point, we get a deadlock error:

ERROR:  40P01: deadlock detected
DETAIL:  Process 13871 waits for ShareLock on transaction 11650227; blocked by process 13860.
Process 13860 waits for ShareLock on transaction 11650229; blocked by process 13871.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (0,10) in relation "users_email"
LOCATION:  DeadLockReport, deadlock.c:1153