Long 2PC 对PostgreSQL的危害

PostgreSQL 数据库中如果有长时间未提交的 2PC，会对数据库造成哪些影响。

rudonx=> begin;
BEGIN
rudonx=> insert into accounts values(1,100);
INSERT 0 1
rudonx=> prepare transaction 'test'; 
PREPARE TRANSACTION
rudonx=> select * from pg_prepared_xacts ;  
 transaction | gid  |           prepared            | owner  | database 
-------------+------+-------------------------------+--------+----------
      287858 | test | 2023-09-14 23:57:07.388915+08 | rudonx | rudonx
(1 row)

rudonx=> select * from txid_current_snapshot();
 txid_current_snapshot 
-----------------------
 287858:287858:
(1 row)

影响一：长期持有锁

运行 alter table，会发现阻塞。同样的，运行 vacuum full 也会阻塞。

rudonx=> alter table accounts ADD COLUMN pc int;

rudonx=> select * from pg_stat_activity\gx
-[ RECORD 9 ]----+--------------------------------------------
datid            | 25554
datname          | rudonx
pid              | 3239666
usesysid         | 24583
usename          | rudonx
application_name | psql
client_addr      | 180.184.86.30
client_hostname  | 
client_port      | 41434
backend_start    | 2023-09-14 23:58:41.377931+08
xact_start       | 2023-09-14 23:59:40.682644+08
query_start      | 2023-09-14 23:59:40.682644+08
state_change     | 2023-09-14 23:59:40.682646+08
wait_event_type  | Lock
wait_event       | relation
state            | active
backend_xid      | 287862
backend_xmin     | 287858
query            | alter table accounts ADD COLUMN pc int;
backend_type     | client backend

影响二：事务 ID 无法被 Freeze

//查看当前数据库年龄
rudonx=> select age(datfrozenxid),datfrozenxid,datname from pg_database where datname='rudonx';
  age   | datfrozenxid | datname 
--------+--------------+---------
 287387 |          482 | rudonx
(1 row)
//插入数据，使事务 ID 增长
rudonx=> insert into accounts values(2,200);
INSERT 0 1
rudonx=> insert into accounts values(3,300);
INSERT 0 1
rudonx=> insert into accounts values(4,400);
INSERT 0 1
rudonx=> select age(datfrozenxid),datfrozenxid,datname from pg_database where datname='rudonx';
  age   | datfrozenxid | datname 
--------+--------------+---------
 287391 |          482 | rudonx
(1 row)

尝试进行 vacuum freeze

rudonx=> select age(datfrozenxid),datfrozenxid,datname from pg_database where datname='rudonx';
  age   | datfrozenxid | datname 
--------+--------------+---------
 287391 |          482 | rudonx
(1 row)

rudonx=> vacuum freeze accounts;
VACUUM
rudonx=> select age(datfrozenxid),datfrozenxid,datname from pg_database where datname='rudonx';
  age   | datfrozenxid | datname 
--------+--------------+---------
 287391 |          482 | rudonx
(1 row)

影响三：dead tuple 无法被回收

因为数据库只能回收实例中现存最老的事务快照之前产生的 dead tuple。Long 2PC 会导致 dead tuple 无法被回收，导致表膨胀。也就是说，如果 dead tuple 是在最早未提交事务之后产生的，则无法被回收。

在生产环境中，建议监控 2PC，并将 2PC 及时提交，避免表膨胀，锁问题以及事务 ID 无法被 Freeze 的问题。

1. 超过 N 秒未结束的 2PC 事务：

rudonx=> select count(*) from pg_prepared_xacts where now()- prepared > interval '5 second';
 count 
-------
     1
(1 row)

2. 查看 2PC 详细信息

rudonx=> select * from pg_prepared_xacts ;  
 transaction | gid  |           prepared            | owner  | database 
-------------+------+-------------------------------+--------+----------
      287858 | test | 2023-09-14 23:57:07.388915+08 | rudonx | rudonx
(1 row)

3. 提交 2PC 事务

rudonx=> COMMIT PREPARED 'test';
COMMIT PREPARED

• PostgreSQL: Documentation: 12: PREPARE TRANSACTION
• PostgreSQL: Documentation: 12: 51.78. pg_prepared_xacts

如果您有其他问题，欢迎您联系火山引擎技术支持服务