分析PostgreSQL中的Prepare Transaction特性

本篇内容介绍了"分析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@localhost pg121db]$ vim postgresql.conf [pg12@localhost pg121db]$ pg_ctl restartwaiting for server to shut down.... doneserver stoppedwaiting for server to start....2020-02-10 15:24:24.979 CST @ 2122  LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2020-02-10 15:24:24.980 CST @ 2122  LOG:  listening on IPv4 address "0.0.0.0", port 51202020-02-10 15:24:24.980 CST @ 2122  LOG:  listening on IPv6 address "::", port 51202020-02-10 15:24:24.985 CST @ 2122  LOG:  listening on Unix socket "/data/run/pg12/.s.PGSQL.5120"2020-02-10 15:24:25.058 CST @ 2122  LOG:  redirecting log output to logging collector process2020-02-10 15:24:25.058 CST @ 2122  HINT:  Future log output will appear in directory "pg_log". doneserver started[pg12@localhost pg121db]$ grep 'prepared' postgresql.conf max_prepared_transactions = 10        # zero disables the feature# Caution: it is not advisable to set max_prepared_transactions nonzero unless# you actively intend to use prepared transactions.[pg12@localhost pg121db]$

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

/* * */package testPG;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Statement;public class TestBoolean {    public static void main(String[] args) {        System.out.println("---------- PG -----------");        try (Connection conn4pg = DriverManager.getConnection("jdbc:postgresql://192.168.26.28:5120/testdb", "pg12",                "pg12");                Connection conn4ora = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.18:1521:orcl", "test",                        "test")) {            // PG            System.out.println("---------- PG -----------");            conn4pg.setAutoCommit(false);            boolean isOK = TestPG(conn4pg);            if (!isOK) {                System.out.println("---------- Fail! -----------");                return;            }            TestPGPreTrans(conn4pg);            // Oracle            System.out.println("---------- Oracle -----------");            conn4ora.setAutoCommit(false);            isOK = TestOracle(conn4ora);            // COMMIT            conn4pg.setAutoCommit(true);            TestPGEndPreTrans(conn4pg, isOK);            conn4ora.commit();            System.out.println("---------- DONE -----------");        } catch (SQLException se) {            System.out.println(se.getMessage());        } catch (Exception e) {            e.printStackTrace();        } finally {        } // end try    }    public static boolean TestPG(Connection conn) {        try (PreparedStatement pstmt = conn.prepareStatement("insert into t_pg(id,value) values(?,?)");) {            pstmt.setInt(1, 1);            pstmt.setString(2, "PostgreSQL");            pstmt.execute();            return true;        } catch (SQLException se) {            System.out.println(se.getMessage());        } catch (Exception e) {            e.printStackTrace();        } finally {        } // end try        return false;    } // end    public static void TestPGPreTrans(Connection conn) {        try (Statement stmt = conn.createStatement()) {            // 执行            stmt.execute("prepare transaction 'pt1'");            stmt.execute("commit");        } catch (SQLException se) {            System.out.println(se.getMessage());        } catch (Exception e) {            e.printStackTrace();        } finally {        } // end try    } // end    public static void TestPGEndPreTrans(Connection conn, Boolean isOK) {        try (Statement stmt = conn.createStatement()) {            // 执行            if (isOK) {                stmt.execute("commit prepared 'pt1'");            } else {                stmt.execute("rollback prepared 'pt1'");            }        } catch (SQLException se) {            System.out.println(se.getMessage());        } catch (Exception e) {            e.printStackTrace();        } finally {        } // end try    } // end    public static boolean TestOracle(Connection conn) {        try (PreparedStatement pstmt = conn.prepareStatement("insert into t_oracle(id,value) values(?,?)");) {            pstmt.setInt(1, 1);            pstmt.setString(2, "Oracle");            pstmt.execute();            return true;        } catch (SQLException se) {            System.out.println(se.getMessage());        } catch (Exception e) {            e.printStackTrace();        } finally {        } // end try        return false;    } // end} // end Class

成功执行

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

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

TEST-orcl@DESKTOP-V430TU3>alter table t_oracle add primary key(id);Table altered.

Java日志输出

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

查询PG数据库

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

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

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