PG外部表的死锁检测测试

PostgreSQL’s FDW does not detect deadlock.
postgres_fdw and the FDW feature do not support the distributed lock manager and the distributed deadlock detection feature. Therefore, a deadlock can be easily generated. For example, if Client_A updates a local table ‘tbl_local’ and a foreign table ‘tbl_remote’ and Client_B updates ‘tbl_remote’ and ‘tbl_local’, these two transactions are in deadlock but could not be detected by PostgreSQL. Therefore these transactions could not be committed.
外部表相关的死锁，不能被PostgreSQL默认的死锁监测机制检测到，需要人工干预才能提交事物。

测试环境

本地：192.168.10.11 PostgreSQL版本14.7
远程：192.168.10.14 PostgreSQL版本11.5

测试过程

1.环境参数配置

192.168.10.11上操作
[postgres@du101 pgdata]$ psql
psql (14.7)
Type "help" for help.

postgres=# show  deadlock_timeout;
 deadlock_timeout
------------------
 10s
(1 row)

postgres=# show log_lock_waits;
 log_lock_waits
----------------
 on
(1 row)

postgres=#

2.验证死锁机制

192.168.10.11上操作
[postgres@du101 pgdata]$ psql
psql (14.7)
Type "help" for help.

postgres=# create table  t(id int,info varchar(20));
CREATE TABLE
postgres=# create table  tt(id int,info varchar(20));
CREATE TABLE
postgres=# insert  into t values(1,'t test'),(2,'t test');
INSERT 0 2
postgres=# insert  into tt values(1,'tt test'),(2,'tt test');
INSERT 0 2
postgres=#

192.168.10.11上操作会话1：

postgres=# begin;
BEGIN
postgres=*# update t set info='t update' where id=1;
UPDATE 1
postgres=*#


192.168.10.11上操作会话2：

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=*# update tt set info='tt update' where id=1;
UPDATE 1
postgres=*#



192.168.10.11上操作会话1：
postgres=*# update tt set info='tt update' where id=1;
此会话等待中


192.168.10.11上操作会话2：

postgres=*# update t set info='t update' where id=1;
ERROR:  deadlock detected
DETAIL:  Process 1426 waits for ShareLock on transaction 738; blocked by process 1428.
Process 1428 waits for ShareLock on transaction 739; blocked by process 1426.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "t"
postgres=!#
postgres=!# rollback;
ROLLBACK
postgres=#

数据库日志如下：

2023-04-04 09:36:27.126 CST,"postgres","postgres",1426,"::1:52500",642b7e09.592,4,"UPDATE",2023-04-04 09:31:5ST,4/6,739,ERROR,40P01,"deadlock detected","Process 1426 waits for ShareLock on transaction 738; blocked by pess 1428.
Process 1428 waits for ShareLock on transaction 739; blocked by process 1426.
Process 1426: update t set info='t update' where id=1;
Process 1428: update tt set info='tt update' where id=1;","See server log for query details.",,,"while updatituple (0,1) in relation ""t""","update t set info='t update' where id=1;",,"DeadLockReport, deadlock.c:1153",ql","client backend",,0

192.168.10.11上操作会话1：
postgres=*# update tt set info='tt update' where id=1;


UPDATE 1
postgres=*#
postgres=*#
postgres=*# rollback;
ROLLBACK
postgres=#

普通表发生死锁会被PostgreSQL默认的死锁检测机制处理，并记录在数据库日志（参数log_lock_waits控制）中。

3.创建外部表

192.168.10.14上操作
[postgres@du104 ~]$ psql
psql (11.5)
Type "help" for help.

postgres=# create table t(id int,name varchar(20));
CREATE TABLE
postgres=# insert into t values(1,'104test');
INSERT 0 1
postgres=# select * from t;
 id |  name
----+---------
  1 | 104test
(1 row)

postgres=#


192.168.10.11上操作
postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER s_pg11 FOREIGN DATA WRAPPER postgres_fdw;
CREATE SERVER
postgres=# alter server s_pg11 options ( add hostaddr '192.168.10.14', add port '1921', add dbname 'postgres');
ALTER SERVER
postgres=# create user mapping for postgres server s_pg11 options(user 'postgres',password 'postgres');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE ft (id int,name varchar(20)) SERVER s_pg11 options(schema_name 'public', table_name 't');
CREATE FOREIGN TABLE
postgres=# select * from ft;
 id |  name
----+---------
  1 | 104test
(1 row)

postgres=#

4.外部表测试死锁

192.168.10.11上操作会话1
postgres=# begin;
BEGIN
postgres=*# update t set info ='t 101update' where id=1;
UPDATE 1
postgres=*#



192.168.10.11上操作会话2
postgres=# begin;
BEGIN
postgres=*# update ft set name='ft 101update' where id=1;
UPDATE 1
postgres=*#


192.168.10.11上操作会话1
postgres=*# update ft set name='ft 101update' where id=1;
此会话等待中


192.168.10.11上操作会话2
postgres=*#  update t set info ='t 101update' where id=1;
此会话等待中

192.168.10.11上操作会话3
[postgres@du101 pg_log]$ psql
psql (14.7)
Type "help" for help.

postgres=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity  where pg_blocking_pids(pid)<>'{}';
 pid  | pg_blocking_pids | wait_event_type |  wait_event   |                    query
------+------------------+-----------------+---------------+----------------------------------------------
 1426 | {1428}           | Lock            | transactionid | update t set info ='t 101update' where id=1;
(1 row)

postgres=# select pid,now()-xact_start xact_time,now()-query_start query_time,wait_event_type,wait_event,state,query from pg_stat_activity where pid<>pg_backend_pid() and query like 'update%';
 pid  |    xact_time    |   query_time    | wait_event_type |  wait_event   | state  |                     query
------+-----------------+-----------------+-----------------+---------------+--------+-----------------------------------------------
 1428 | 00:07:50.886643 | 00:06:30.734957 | Extension       | Extension     | active | update ft set name='ft 101update' where id=1;
 1426 | 00:07:09.000129 | 00:06:06.816477 | Lock            | transactionid | active | update t set info ='t 101update' where id=1;
(2 rows)

postgres=#

SQL的执行时间早就超过了参数deadlock_timeout设置的10s中，但由于这是有外部表参与的死锁，并没有被PostgreSQL的死锁检测机制处理，只是以锁等待的方式呈现出来。