Simple distributed deadlock scenario
From PostgreSQL wiki
Jump to navigationJump to search
Creating simple distributed deadlock scenario
This page demonstrates how to create deadlock spanning among different database server using postgresql_fdw. Now we cannot detect deadlocks in this scenario.
-- Initialization
--
-- Servers:
-- km2-pg00 -- server km2_pg00
-- km2-pg01 -- server km2_pg01
--
-- Database/user
-- -- koichi/koichi
--
-- Initialization of km2-pg00
koichi=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)
koichi=# create extension postgres_fdw;
CREATE EXTENSION
koichi=# CREATE TABLE t1 (c0 int primary key, c1 text);
CREATE TABLE
koichi=# INSERT INTO t1 values (0, 'abc'), (1, 'bcd');
INSERT 0 2
koichi=# CREATE TABLE t2 (c0 int primary key, c1 text);
CREATE TABLE
koichi=# INSERT INTO t2 values (0, 'abc'), (1, 'bcd');
INSERT 0 2
koichi=# CREATE SERVER km2_pg01 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'km2-pg01', port '5432', dbname 'koichi');
CREATE SERVER
koichi=# CREATE USER MAPPING for koichi SERVER km2_pg01 OPTIONS (user 'koichi');
CREATE USER MAPPING
koichi=# CREATE FOREIGN TABLE km2_pg01_t1 (c0 int, c1 text) SERVER km2_pg01 OPTIONS(table_name 't1');
CREATE FOREIGN TABLE
koichi=# CREATE FOREIGN TABLE km2_pg01_t2 (c0 int, c1 text) SERVER km2_pg01 OPTIONS(table_name 't2');
CREATE FOREIGN TABLE
koichi=#
koichi=# select * from t1;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from t2;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from km2_pg01_t1;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from km2_pg01_t2;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=#
- Initialization of km2-pg01
koichi=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)
koichi=# create extension postgres_fdw;
CREATE EXTENSION
koichi=#
koichi=# CREATE TABLE t1 (c0 int primary key, c1 text);
CREATE TABLE
koichi=# INSERT INTO t1 values (0, 'abc'), (1, 'bcd');
INSERT 0 2
koichi=# CREATE TABLE t2 (c0 int primary key, c1 text);
CREATE TABLE
koichi=# INSERT INTO t2 values (0, 'abc'), (1, 'bcd');
INSERT 0 2
koichi=# CREATE SERVER km2_pg00 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'km2-pg00', port '5432', dbname 'koichi');
CREATE SERVER
koichi=# CREATE USER MAPPING for koichi SERVER km2_pg00 OPTIONS (user 'koichi');
CREATE USER MAPPING
koichi=# CREATE FOREIGN TABLE km2_pg00_t1 (c0 int, c1 text) SERVER km2_pg00 OPTIONS(table_name 't1');
CREATE FOREIGN TABLE
koichi=# CREATE FOREIGN TABLE km2_pg00_t2 (c0 int, c1 text) SERVER km2_pg00 OPTIONS(table_name 't2');
CREATE FOREIGN TABLE
koichi=#
koichi=# select * from t1;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from t2;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from km2_pg00_t1;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=# select * from km2_pg00_t2;
c0 | c1
----+-----
0 | abc
1 | bcd
(2 rows)
koichi=#
### ********** Testing local deadlock and its detection locally at km2-pg00 *************
T0 T1
-----------------------------------------------------------------------------------------------------------
koichi=# begin;
BEGIN
koichi=*#
koichi=# begin;
BEGIN
koichi=*#
koichi=*# insert into t1 values (2, 'cde');
INSERT 0 1
koichi=*#
koichi=*# insert into t2 values (2, 'xyz');
INSERT 0 1
koichi=*#
koichi=*# insert into t2 values (2, 'cde');
-- T0 waiting T1 to release its lock on t2.
koichi=*# insert into t1 values (2, 'xyz');
ERROR: deadlock detected
DETAIL: Process 12575 waits for ShareLock on transaction 780; blocked by process 12517.
Process 12517 waits for ShareLock on transaction 781; blocked by process 12575.
HINT: See server log for query details.
CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
koichi=!#
-- Deadlock detected and T1 aborted
INSERT 0 1
koichi=*#
-- T1 abort and then T0 move on. Insert successful.
### Database log at km2-pg00 about the above deadlock
---
2025-12-12 15:11:06.725 JST [12517] LOG: process 12517 still waiting for ShareLock on transaction 781 after 1133.204 ms
2025-12-12 15:11:06.725 JST [12517] DETAIL: Process holding the lock: 12575. Wait queue: 12517.
2025-12-12 15:11:06.725 JST [12517] CONTEXT: while inserting index tuple (0,5) in relation "t2_pkey"
2025-12-12 15:11:06.725 JST [12517] STATEMENT: insert into t2 values (2, 'cde');
2025-12-12 15:11:46.007 JST [12575] LOG: process 12575 detected deadlock while waiting for ShareLock on transaction 780 after 1026.605 ms
2025-12-12 15:11:46.007 JST [12575] DETAIL: Process holding the lock: 12517. Wait queue: .
2025-12-12 15:11:46.007 JST [12575] CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
2025-12-12 15:11:46.007 JST [12575] STATEMENT: insert into t1 values (2, 'xyz');
2025-12-12 15:11:46.007 JST [12575] ERROR: deadlock detected
2025-12-12 15:11:46.007 JST [12575] DETAIL: Process 12575 waits for ShareLock on transaction 780; blocked by process 12517.
Process 12517 waits for ShareLock on transaction 781; blocked by process 12575.
Process 12575: insert into t1 values (2, 'xyz');
Process 12517: insert into t2 values (2, 'cde');
2025-12-12 15:11:46.007 JST [12575] HINT: See server log for query details.
2025-12-12 15:11:46.007 JST [12575] CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
2025-12-12 15:11:46.007 JST [12575] STATEMENT: insert into t1 values (2, 'xyz');
2025-12-12 15:11:46.007 JST [12517] LOG: process 12517 acquired ShareLock on transaction 781 after 40414.830 ms
2025-12-12 15:11:46.007 JST [12517] CONTEXT: while inserting index tuple (0,5) in relation "t2_pkey"
2025-12-12 15:11:46.007 JST [12517] STATEMENT: insert into t2 values (2, 'cde');
---
### ********** Testing remote deadlock and its detection using foreign table at km2-pg01 from km2-pg00 *************
T0 T1
-----------------------------------------------------------------------------------------------------------
koichi=# begin;
BEGIN
koichi=*#
koichi=# begin;
BEGIN
koichi=*#
koichi=*# insert into km2_pg01_t1 values (2, 'cde');
INSERT 0 1
koichi=*#
koichi=*# insert into km2_pg01_t2 values (2, 'xyz');
INSERT 0 1
koichi=*#
koichi=*# insert into km2_pg01_t2 values (2, 'cde');
-- T0 waiting T1 to release its lock on t2.
koichi=*# insert into km2_pg01_t1 values (2, 'xyz');
ERROR: deadlock detected
DETAIL: Process 9229 waits for ShareLock on transaction 778; blocked by process 9227.
Process 9227 waits for ShareLock on transaction 779; blocked by process 9229.
HINT: See server log for query details.
CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
remote SQL command: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
koichi=!#
-- Deadlock detected and T1 aborted
INSERT 0 1
koichi=*#
-- T1 abort and then T0 move on. Insert successful.
-- Deadlock was detected as expected. The wait-for-graph was local to km2-pg01.
-- Corresponding database log at km2-pg00
2025-12-12 15:21:26.992 JST [12784] ERROR: deadlock detected
2025-12-12 15:21:26.992 JST [12784] DETAIL: Process 9229 waits for ShareLock on transaction 778; blocked by process 9227.
Process 9227 waits for ShareLock on transaction 779; blocked by process 9229.
2025-12-12 15:21:26.992 JST [12784] HINT: See server log for query details.
2025-12-12 15:21:26.992 JST [12784] CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
remote SQL command: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
2025-12-12 15:21:26.992 JST [12784] STATEMENT: insert into km2_pg01_t1 values (2, 'xyz');
-- Corresponding database log at km2-pg01
-- Please note that Wait-for-Graph tracking information appears in the remote database
2025-12-12 15:20:52.615 JST [9227] LOG: process 9227 still waiting for ShareLock on transaction 779 after 1203.239 ms
2025-12-12 15:20:52.615 JST [9227] DETAIL: Process holding the lock: 9229. Wait queue: 9227.
2025-12-12 15:20:52.615 JST [9227] CONTEXT: while inserting index tuple (0,5) in relation "t2_pkey"
2025-12-12 15:20:52.615 JST [9227] STATEMENT: INSERT INTO public.t2(c0, c1) VALUES ($1, $2)
2025-12-12 15:21:26.992 JST [9229] LOG: process 9229 detected deadlock while waiting for ShareLock on transaction 778 after 1078.160 ms
2025-12-12 15:21:26.992 JST [9229] DETAIL: Process holding the lock: 9227. Wait queue: .
2025-12-12 15:21:26.992 JST [9229] CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
2025-12-12 15:21:26.992 JST [9229] STATEMENT: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
2025-12-12 15:21:26.992 JST [9229] ERROR: deadlock detected
2025-12-12 15:21:26.992 JST [9229] DETAIL: Process 9229 waits for ShareLock on transaction 778; blocked by process 9227.
Process 9227 waits for ShareLock on transaction 779; blocked by process 9229.
Process 9229: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
Process 9227: INSERT INTO public.t2(c0, c1) VALUES ($1, $2)
2025-12-12 15:21:26.992 JST [9229] HINT: See server log for query details.
2025-12-12 15:21:26.992 JST [9229] CONTEXT: while inserting index tuple (0,5) in relation "t1_pkey"
### ********** Testing a deadlock between a foreign table at km2-pg01 and a local table at km2-pg00 *************
T0 T1
-----------------------------------------------------------------------------------------------------------
koichi=# begin;
BEGIN
koichi=*#
koichi=# begin;
BEGIN
koichi=*#
koichi=*# insert into t1 values (2, 'cde');
INSERT 0 1
koichi=*#
koichi=*# insert into km2_pg01_t1 values (2, 'xyz');
INSERT 0 1
koichi=*#
koichi=*# insert into km2_pg01_t1 values (2, 'cde');
-- T0 waiting T1 to release its lock on km2_pg01_t1
koichi=*# insert into t1 values (2, 'xyz');
-- T1 waiting for T0 to release the lock on t1.
-- Wait-for-graph spanns over km2-pg00 and km2-pg01. Current PG cannot track this global wait-for-graph and cannot
-- detect the above deadlock scenario. Both transactions are waiting for each other forever.
-- We need to abort T0 or T1 to move things forward. Let's abort T1.
^CCancel request sent
ERROR: canceling statement due to user request
CONTEXT: while inserting index tuple (0,7) in relation "t1_pkey"
koichi=!#
koichi=*# insert into km2_pg01_t1 values (2, 'cde');
INSERT 0 1
koichi=*#
-- T0 continues and run successfully.
-- Corresponding database log at km2-pg00
-- Note that only local wait-for-graph tracking was logged.
2025-12-12 15:31:26.620 JST [12784] STATEMENT: insert into km2_pg01_t1 values (2, 'xyz');
2025-12-12 15:33:52.009 JST [12837] LOG: process 12837 still waiting for ShareLock on transaction 782 after 1193.399 ms
2025-12-12 15:33:52.009 JST [12837] DETAIL: Process holding the lock: 12841. Wait queue: 12837.
2025-12-12 15:33:52.009 JST [12837] CONTEXT: while inserting index tuple (0,7) in relation "t1_pkey"
2025-12-12 15:33:52.009 JST [12837] STATEMENT: insert into t1 values (2, 'xyz');
2025-12-12 15:35:01.163 JST [12047] LOG: checkpoint starting: time
2025-12-12 15:35:01.475 JST [12047] LOG: checkpoint complete: wrote 2 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.255 s, sync=0.016 s, total=0.312 s; sync files=2, longest=0.010 s, average=0.008 s; distance=0 kB, estimate=121 kB; lsn=0/1C90230, redo lsn=0/1C901D0
2025-12-12 15:36:28.827 JST [12837] ERROR: canceling statement due to user request
2025-12-12 15:36:28.827 JST [12837] CONTEXT: while inserting index tuple (0,7) in relation "t1_pkey"
2025-12-12 15:36:28.827 JST [12837] STATEMENT: insert into t1 values (2, 'xyz');
-- Corresponding database log at km2-pg01
-- Note that only local wait-for-graph tracking was logged.
2025-12-12 15:33:20.739 JST [9389] LOG: process 9389 still waiting for ShareLock on transaction 783 after 1004.627 ms
2025-12-12 15:33:20.739 JST [9389] DETAIL: Process holding the lock: 9380. Wait queue: 9389.
2025-12-12 15:33:20.739 JST [9389] CONTEXT: while inserting index tuple (0,10) in relation "t1_pkey"
2025-12-12 15:33:20.739 JST [9389] STATEMENT: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
2025-12-12 15:36:28.827 JST [9389] LOG: process 9389 acquired ShareLock on transaction 783 after 189092.345 ms
2025-12-12 15:36:28.827 JST [9389] CONTEXT: while inserting index tuple (0,10) in relation "t1_pkey"
2025-12-12 15:36:28.827 JST [9389] STATEMENT: INSERT INTO public.t1(c0, c1) VALUES ($1, $2)
