关于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 disabledHINT:  Set max_prepared_transactions to a nonzero value.

prepare transaction

postgres=# begin;BEGINpostgres=# insert into t1 values(666, 'test666');INSERT 0 1postgres=# select * from t1 where id=666; id  |  name-----+--------- 666 | test666(1 row)postgres=# prepare transaction 'prep1';PREPARE TRANSACTIONpostgres=# 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.1psql (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 PREPAREDpostgres=# 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;BEGINpostgres=# insert into t1 values(777, 'test777');INSERT 0 1postgres=# prepare transaction 'prep2';PREPARE TRANSACTIONpostgres=#  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 PREPAREDpostgres=#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...