ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(160) - pgAdmin(prepare transaction)

PostgreSQL DBA(160) - pgAdmin(prepare transaction)

原创 PostgreSQL 作者:husthxd 时间:2020-02-10 16:58:18 0 删除 编辑

本节简单介绍了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 restart
waiting for server to shut down.... done
server stopped
waiting 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-bit
2020-02-10 15:24:24.980 CST @ 2122  LOG:  listening on IPv4 address "0.0.0.0", port 5120
2020-02-10 15:24:24.980 CST @ 2122  LOG:  listening on IPv6 address "::", port 5120
2020-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 process
2020-02-10 15:24:25.058 CST @ 2122  HINT:  Future log output will appear in directory "pg_log".
 done
server 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条记录,实现了跨数据库的事务一致性。

参考资料
Prepared Transactions in PostgreSQL 9.2
PREPARE TRANSACTION

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2675017/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1488
  • 访问量
    3925436