本節簡單介紹了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語言編寫測試程式碼
```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 ](http://chirupgadmin.blogspot.com/2013/05/prepared-transactions-in-postgresql.html)
[PREPARE TRANSACTION](http://www.postgres.cn/docs/11/sql-prepare-transaction.html)

PostgreSQL DBA(160) - pgAdmin(prepare transaction)

相關文章