本篇内容介绍了“分析PostgreSQL中的Prepare Transaction特性”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

比如以下一个应用场景:
数据分别存储在Oracle和PostgreSQL中,要求事务跨Oracle和PostgreSQL实现事务一致性,使用PG的Prepare Transaction可以(非完美)实现,不过需要引入更高层的事务管理器TM.
1.TM:开启PostgreSQL和Oracle事务
2.PostgreSQL:对数据进行处理
3.TM:对PG执行Prepare Transaction
4.Oracle:对数据进行处理
5.TM:PG提交事务
6.TM:如第5步出错,则回滚Oracle事务,否则提交Oracle事务

启用两阶段提交特性

[pg12@localhostpg121db]$vimpostgresql.conf[pg12@localhostpg121db]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2020-02-1015:24:24.979CST@2122LOG:startingPostgreSQL12.1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2020-02-1015:24:24.980CST@2122LOG:listeningonIPv4address"0.0.0.0",port51202020-02-1015:24:24.980CST@2122LOG:listeningonIPv6address"::",port51202020-02-1015:24:24.985CST@2122LOG:listeningonUnixsocket"/data/run/pg12/.s.PGSQL.5120"2020-02-1015:24:25.058CST@2122LOG:redirectinglogoutputtologgingcollectorprocess2020-02-1015:24:25.058CST@2122HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhostpg121db]$grep'prepared'postgresql.confmax_prepared_transactions=10#zerodisablesthefeature#Caution:itisnotadvisabletosetmax_prepared_transactionsnonzerounless#youactivelyintendtousepreparedtransactions.[pg12@localhostpg121db]$

测试代码
使用Java语言编写测试代码

/***/packagetestPG;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.sql.Statement;publicclassTestBoolean{publicstaticvoidmain(String[]args){System.out.println("----------PG-----------");try(Connectionconn4pg=DriverManager.getConnection("jdbc:postgresql://192.168.26.28:5120/testdb","pg12","pg12");Connectionconn4ora=DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.18:1521:orcl","test","test")){//PGSystem.out.println("----------PG-----------");conn4pg.setAutoCommit(false);booleanisOK=TestPG(conn4pg);if(!isOK){System.out.println("----------Fail!-----------");return;}TestPGPreTrans(conn4pg);//OracleSystem.out.println("----------Oracle-----------");conn4ora.setAutoCommit(false);isOK=TestOracle(conn4ora);//COMMITconn4pg.setAutoCommit(true);TestPGEndPreTrans(conn4pg,isOK);conn4ora.commit();System.out.println("----------DONE-----------");}catch(SQLExceptionse){System.out.println(se.getMessage());}catch(Exceptione){e.printStackTrace();}finally{}//endtry}publicstaticbooleanTestPG(Connectionconn){try(PreparedStatementpstmt=conn.prepareStatement("insertintot_pg(id,value)values(?,?)");){pstmt.setInt(1,1);pstmt.setString(2,"PostgreSQL");pstmt.execute();returntrue;}catch(SQLExceptionse){System.out.println(se.getMessage());}catch(Exceptione){e.printStackTrace();}finally{}//endtryreturnfalse;}//endpublicstaticvoidTestPGPreTrans(Connectionconn){try(Statementstmt=conn.createStatement()){//执行stmt.execute("preparetransaction'pt1'");stmt.execute("commit");}catch(SQLExceptionse){System.out.println(se.getMessage());}catch(Exceptione){e.printStackTrace();}finally{}//endtry}//endpublicstaticvoidTestPGEndPreTrans(Connectionconn,BooleanisOK){try(Statementstmt=conn.createStatement()){//执行if(isOK){stmt.execute("commitprepared'pt1'");}else{stmt.execute("rollbackprepared'pt1'");}}catch(SQLExceptionse){System.out.println(se.getMessage());}catch(Exceptione){e.printStackTrace();}finally{}//endtry}//endpublicstaticbooleanTestOracle(Connectionconn){try(PreparedStatementpstmt=conn.prepareStatement("insertintot_oracle(id,value)values(?,?)");){pstmt.setInt(1,1);pstmt.setString(2,"Oracle");pstmt.execute();returntrue;}catch(SQLExceptionse){System.out.println(se.getMessage());}catch(Exceptione){e.printStackTrace();}finally{}//endtryreturnfalse;}//end}//endClass

成功执行

TEST-orcl@DESKTOP-V430TU3>select*fromt_oracle;IDVALUE------------------------------1Oracle[local:/data/run/pg12]:5120pg12@testdb=#select*fromt_pg;id|value----+------------1|PostgreSQL(1row)

执行失败
Oracle数据表添加唯一索引，插入会失败。

TEST-orcl@DESKTOP-V430TU3>altertablet_oracleaddprimarykey(id);Tablealtered.

Java日志输出

----------PG---------------------PG---------------------Oracle-----------ORA-00001:违反唯一约束条件(TEST.SYS_C0064492)----------DONE-----------

查询PG数据库

[local:/data/run/pg12]:5120pg12@testdb=#select*fromt_pg;id|value----+------------1|PostgreSQL(1row)

仍然是1条记录，实现了跨数据库的事务一致性。

“分析PostgreSQL中的Prepare Transaction特性”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！