Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "analyzing the characteristics of Prepare Transaction in PostgreSQL". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

For example, the following application scenario:

Data is stored in Oracle and PostgreSQL respectively, which requires transactions to achieve transaction consistency across Oracle and PostgreSQL, which can be (imperfectly) implemented using PG's Prepare Transaction, but it needs to introduce a higher-level transaction manager TM.

1.TM: open PostgreSQL and Oracle transactions

2.PostgreSQL: processing data

3.TM: perform Prepare Transaction on PG

4.Oracle: processing data

5.TM:PG commit transaction

6.TM: if an error occurs in step 5, roll back the Oracle transaction, otherwise commit the Oracle transaction

Enable the two-phase commit feature

[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 on x86_64-pc-linux-gnu 24 LOG 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 15 listening on IPv4 address 24.980 CST @ 2122 LOG: listening on IPv4 address "0.0.0.0", port 51202020-02-10 15V 24V 24.980 CST @ 2122 LOG: listening on IPv6 address ":" Port 51202020-02-10 15 port 24 port 24.985 CST @ 2122 LOG: listening on Unix socket "/ data/run/pg12/.s.PGSQL.5120" 2020-02-10 15 15 pg_log 24 pg_log 25.058 CST @ 2122 LOG: redirecting log output to logging collector process2020-02-10 15 15 24 Swiss 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] $

Test code

Write test code in the Java language

/ * * / 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 ()) {/ / execute 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 ()) {/ / execute 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

Successful execution

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

Execution failed

Add a unique index to the Oracle data table and the insert will fail.

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

Java log output

-PG-PG-Oracle-ORA-00001: violation of unique constraints (TEST.SYS_C0064492)-DONE-

Query PG database

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

It is still a record, achieving transaction consistency across databases.

That's all for "analyzing Prepare Transaction features in PostgreSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.