本篇內(nèi)容介紹了“分析PostgreSQL中的Prepare Transaction特性”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
尉犁ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書未來市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書合作）期待與您的合作！
比如以下一個(gè)應(yīng)用場(chǎng)景:
數(shù)據(jù)分別存儲(chǔ)在Oracle和PostgreSQL中,要求事務(wù)跨Oracle和PostgreSQL實(shí)現(xiàn)事務(wù)一致性,使用PG的Prepare Transaction可以(非完美)實(shí)現(xiàn),不過需要引入更高層的事務(wù)管理器TM.
1.TM:開啟PostgreSQL和Oracle事務(wù)
2.PostgreSQL:對(duì)數(shù)據(jù)進(jìn)行處理
3.TM:對(duì)PG執(zhí)行Prepare Transaction
4.Oracle:對(duì)數(shù)據(jù)進(jìn)行處理
5.TM:PG提交事務(wù)
6.TM:如第5步出錯(cuò),則回滾Oracle事務(wù),否則提交Oracle事務(wù)
啟用兩階段提交特性
[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]$
測(cè)試代碼
使用Java語言編寫測(cè)試代碼
重慶分公司
重慶分公司
/*
*
*/
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()) {
// 執(zhí)行
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()) {
// 執(zhí)行
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成功執(zhí)行
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)
執(zhí)行失敗
Oracle數(shù)據(jù)表添加唯一索引，插入會(huì)失敗。
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數(shù)據(jù)庫
[local:/data/run/pg12]:5120 pg12@testdb=# select * from t_pg; id | value ----+------------ 1 | PostgreSQL (1 row)
仍然是1條記錄，實(shí)現(xiàn)了跨數(shù)據(jù)庫的事務(wù)一致性。
“分析PostgreSQL中的Prepare Transaction特性”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
