deadlock.markdown

PostgreSQL Deadlock

We started to get deadlock detected errors starting in PostgreSQL 9.3. They did not occur before we migrated from version 9.1. Below are steps to reproduce using pgbench.

System Information

• AWS EC2 m3.medium
• Ubuntu 12.04 64-bit
• PostgreSQL 9.3.5-1.pgdg12.4+1 from this apt repository

We've tested this on 9.1, 9.2, 9.3, and 9.4 beta. The issue does not occur in 9.1 and 9.2.

Set up database and schema

Install PostgreSQL:

$ sudo apt-get install -y postgresql-9.3 postgresql-contrib-9.3

Create a database for pgbench. Note the custom schema:

$ sudo -u postgres createdb deadlock

$ cat <<EOF > create.sql
CREATE SCHEMA deadlock_test;
CREATE TABLE deadlock_test.access_grants (id serial primary key, last_issued timestamp without time zone not null default timezone('UTC'::text, now()));
CREATE TABLE deadlock_test.access_tokens (id serial primary key, access_id bigint references deadlock_test.access_grants(id) on delete cascade);
INSERT INTO deadlock_test.access_grants (id) VALUES (default) RETURNING id;
EOF

$ sudo -u postgres psql -f create.sql deadlock
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
 id
----
  1

Run pgbench

This is the query that we want to perform concurrently. Note that it's a simplified version of a more complicated query, that's why it looks silly.

$ cat <<EOF > query.sql
UPDATE deadlock_test.access_grants ag SET last_issued=DEFAULT RETURNING ag.id;
INSERT INTO deadlock_test.access_tokens (access_id) VALUES (1);
EOF

Now run the custom query with pgbench using a large number of clients and threads.

$ sudo -u postgres /usr/lib/postgresql/9.3/bin/pgbench -c 100 -C -j 100 -n -T 30 -f query.sql deadlock
Client 6 aborted in state 0: ERROR:  deadlock detected
DETAIL:  Process 29218 waits for ShareLock on transaction 21745; blocked by process 29223.
Process 29223 waits for ExclusiveLock on tuple (1,41) of relation 18241 of database 17934; blocked by process 29218.
HINT:  See server log for query details.
Client 31 aborted in state 0: ERROR:  deadlock detected
DETAIL:  Process 29222 waits for ExclusiveLock on tuple (1,41) of relation 18241 of database 17934; blocked by process 29213.
Process 29213 waits for ShareLock on transaction 21745; blocked by process 29223.
Process 29223 waits for ExclusiveLock on tuple (1,41) of relation 18241 of database 17934; blocked by process 29222.
HINT:  See server log for query details.
Client 35 aborted in state 0: ERROR:  deadlock detected
DETAIL:  Process 29223 waits for ExclusiveLock on tuple (1,41) of relation 18241 of database 17934; blocked by process 29213.
Process 29213 waits for ShareLock on transaction 21745; blocked by process 29223.
HINT:  See server log for query details.