关于postgresql:postgresql-prepare-transaction

一般事务为 session 级别，prepare transaction 将以后事务与以后 session 拆散，进行长久化记录，带来的益处是进步事务提交胜利概率。

一般事务是 session 级别的，在 commit 过程中，如果 server 产生异样，如零碎解体，断电等，session 隐没，commit 失败。而 prepare transaction 是长久化记录的，即便产生零碎解体，重启后事务依然能够进行 commit。

prepare transaction 次要用于实现两阶段提交，个别用于事务管理器应用，一般 client 不要间接应用，应该应用 begin，start transaction。

前提：零碎参数 max_prepared_transactions 须要大于 0
设置办法见 alter system

如果 max_prepared_transactions 为 0 会报错

postgres=# prepare transaction 'prep1';
ERROR:  prepared transactions are disabled
HINT:  Set max_prepared_transactions to a nonzero value.

prepare transaction

postgres=# begin;
BEGIN
postgres=# insert into t1 values(666, 'test666');
INSERT 0 1
postgres=# select * from t1 where id=666;
 id  |  name
-----+---------
 666 | test666
(1 row)

postgres=# prepare transaction 'prep1';
PREPARE TRANSACTION
postgres=# select * from pg_prepared_xacts;
 transaction |  gid  |           prepared            |  owner   | database
-------------+-------+-------------------------------+----------+----------
        3316 | prep1 | 2021-06-25 01:48:39.618381+00 | postgres | postgres
(1 row)

postgres=# \q

commit prepared
能够在 prepare transaction 的 session 中执行，也可在其它 session 中执行，也能够在 server 重启后执行。

[postgres@hgcndn ~]$ psql -p 5432 -U postgres -h 127.0.0.1
psql (12.4)
Type "help" for help.

postgres=#  select * from pg_prepared_xacts;
 transaction |  gid  |           prepared            |  owner   | database
-------------+-------+-------------------------------+----------+----------
        3316 | prep1 | 2021-06-25 01:48:39.618381+00 | postgres | postgres
(1 row)

postgres=# select * from t1 where id=666;
 id | name
----+------
(0 rows)

postgres=# commit prepared 'prep1';
COMMIT PREPARED

postgres=# select * from t1 where id=666;
 id  |  name
-----+---------
 666 | test666
(1 row)

postgres=# select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

rollback prepared
能够在 prepare transaction 的 session 中执行，也可在其它 session 中执行，也能够在 server 重启后执行。

postgres=# begin;
BEGIN
postgres=# insert into t1 values(777, 'test777');
INSERT 0 1
postgres=# prepare transaction 'prep2';
PREPARE TRANSACTION
postgres=#  select * from pg_prepared_xacts;
 transaction |  gid  |           prepared            |  owner   | database
-------------+-------+-------------------------------+----------+----------
        3317 | prep2 | 2021-06-25 02:21:52.226903+00 | postgres | postgres
(1 row)

postgres=#
postgres=# rollback prepared 'prep2';
ROLLBACK PREPARED
postgres=#
postgres=#  select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

参考资料
https://www.postgresql.org/do…
https://www.postgresql.org/do…
https://www.postgresql.org/do…