由 John Doe 十二月 18, 2024
摘要：在本文中，我们将了解 PostgreSQL 中使用预备事务的一些注意事项。
目录
介绍
PostgreSQL 为您提供了对两阶段提交的支持。如果你想要进行原子性分布式提交，你可能需要它。如果你查看 PostgreSQL 文档，就会发现关于使用这类事务的明确警告：“除非你正在编写一个事务管理器，否则你可能不应该使用 PREPARE TRANSACTION”。如果你真的需要使用它们，你需要非常小心，预备事务要能尽快提交或回滚。换句话说，您需要一种机制来监视数据库中的预备事务，并在它们保持打开状态的时间过长时，采取适当的措施。如果发生这种情况，您会遇到各种问题，并且您的问题来源也不明显。
使用预备事务的注意事项
首先，让我们来创建一个简单的预备事务：
begin;
create table t1 (a int);
insert into t1 values (1);
prepare transaction 'abc';
从此时起，该事务不再与会话关联。如果您尝试提交或回滚事务，就可以轻松验证这一点：
commit;
WARNING: there is no transaction in progress
这也意味着，在我们预备好事务前创建的 “t1” 表对我们来说是不可见的：
select * from t1;
ERROR: relation "t1" does not exist
LINE 1: select * from t1;
^
虽然我们不再处于任何可见的事务中，但由于我们预备好的事务，在后台还持有锁：
select * from pg_locks
where database = (select oid from pg_database where datname = 'postgres')
and mode like '%Exclusive%';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+---------------------+---------+----------+-----------
relation | 12969 | 24582 | | | | | | | | -1/562 | | RowExclusiveLock | t | f |
relation | 12969 | 24582 | | | | | | | | -1/562 | | AccessExclusiveLock | t | f |
(2 rows)
有一个 AccessExclusiveLock 锁，它是 “t1” 表上的锁。另一个锁 “RowExclusiveLock”，它是保护我们在上面插入的行的锁。我们怎么知道呢？目前这只是一个猜测，因为 “t1” 表是不可见的：
select relname from pg_class where oid = 24582;
relname
---------
(0 rows)
在提交预备好的事务后，我们可以验证它是否真的是关于 “t1” 的：
commit prepared 'abc';
select relname from pg_class where oid = 24582;
relname
---------
t1
(1 row)
select * from t1;
a
---
1
(1 row)
我们还可以通过再次查看锁来确认：
select * from pg_locks
where database = (select oid from pg_database where datname = 'postgres')
and mode like '%Exclusive%';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------+-----------
(0 rows)
这些锁也消失了。所以，也没有多大的事，只要预备好的事务被提交，一切都很好。这是一个很好的情况，如果是这样，您将不会遇到任何问题。
让我们来创建另一个预备事务：
begin;
insert into t1 values(2);
prepare transaction 'abc';
首先要记住的一点是：一旦你创建了一个预备事务，它就会完全存储在磁盘上：
ls -la $PGDATA/pg_twophase/*
-rw------- 1 postgres postgres 212 Feb 26 11:24 /db/pgsql/data/pg_twophase/00000233
提交事务后，文件会消失：
commit prepared 'abc';
ls -la $PGDATA/pg_twophase/
drwx------ 2 postgres postgres 4096 Feb 26 11:26 .
drwx------ 20 postgres postgres 4096 Feb 26 10:49 ..
为什么？答案是，一个预备好的事务，甚至可以在服务器崩溃后进行提交或回滚。但这也意味着，预备事务在实例重启后还是持久存在的：
begin;
insert into t1 values(3);
prepare transaction 'abc';
$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
$ ls -la $PGDATA/pg_twophase/
drwx------ 2 postgres postgres 4096 Feb 26 11:28 .
drwx------ 20 postgres postgres 4096 Feb 26 11:28 ..
-rw------- 1 postgres postgres 212 Feb 26 11:28 00000234
这是个问题吗？想象一下，有人预备了一个事务，但忘记提交或回滚该事务。几天后，有人想要修改应用程序，并尝试向 “t1” 表添加一列：
ALTER TABLE t1 ADD COLUMN b text;
该操作会无缘无故地被阻塞住。再次来查看锁：
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------+-------------------------------
relation | 12969 | 24582 | | | | | | | | 3/4 | 10591 | AccessExclusiveLock | f | f | 2021-02-26 11:30:30.303512+01
relation | 12969 | 24582 | | | | | | | | -1/564 | | RowExclusiveLock | t | f |
(2 rows)
我们可以看到 pid 为 10591 的进程正在尝试获取锁，但无法获得（granted = ‘f’）。另一个条目没有关联的 pid 编号，这就是预备事务。对于预备事务，pid 会始终为空，因此如果您已经知道这一点，它可能会为您指出正确的解决方案。如果你还没有，那么你很可能会被困住。没有可以终止的会话，因为 pg_stat_activity 中没有关于此的报告：
select datid, datname, pid, wait_event_type, wait_event, state, backend_type from pg_stat_activity;
datid | datname | pid | wait_event_type | wait_event | state | backend_type
-------+----------+-------+-----------------+---------------------+--------+------------------------------
| | 10582 | Activity | AutoVacuumMain | | autovacuum launcher
| | 10584 | Activity | LogicalLauncherMain | | logical replication launcher
12969 | postgres | 10591 | Lock | relation | active | client backend
12969 | postgres | 10593 | | | active | client backend
| | 10580 | Activity | BgWriterHibernate | | background writer
| | 10579 | Activity | CheckpointerMain | | checkpointer
| | 10581 | Activity | WalWriterMain | | walwriter
(7 rows)
您将不会看到任何阻塞会话（blocked_by = 0）：
select pid
, usename
, pg_blocking_pids(pid) as blocked_by
, query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
pid | usename | blocked_by | blocked_query
-------+----------+------------+-----------------------------------
10591 | postgres | {0} | ALTER TABLE t1 ADD COLUMN b text;
即使您重新启动实例，问题也会持续存在。唯一的解决方案是提交或回滚预备好的事务；
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+-------------------------------+----------+----------
564 | abc | 2021-02-26 11:28:37.362649+01 | postgres | postgres
(1 row)
rollback prepared 'abc';
一旦事务完成，另一个会话就能够完成它的工作：
ALTER TABLE t1 ADD COLUMN b text;
请记住：当事情看起来真的很奇怪时，可能是因为您有正在进行的预备事务。