分析PostgreSQL中的PrepareTransaction特性
本篇内容介绍了“分析PostgreSQL中的Prepare Transaction特性”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
尉犁ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！
比如以下一个应用场景:
数据分别存储在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条记录，实现了跨数据库的事务一致性。
“分析PostgreSQL中的Prepare Transaction特性”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
本文标题：分析PostgreSQL中的PrepareTransaction特性
网站链接：http://scyanting.com/article/gehssh.html
