postgresql: prepare transaction postgresql

普通事务为session级别，prepare transaction将当前事务与当前session分离，进行持久化记录，带来的好处是提高事务提交成功概率。
【postgresql: prepare transaction】普通事务是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 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; 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...