How to handle duplicate transactions after a database failover in your Oracle RAC environment or Distributed env?
The moment of truth; did the transaction commit or not? is a classic high-availability headache.
Introduction to Oracle Transaction Guard:
This powerful feature is the key to achieving at-most-once execution for your critical business logic, even when a connection drops mid-commit.
Oracle Transaction Guard is a feature designed to provide a reliable way to determine the outcome of a distributed transaction in cases where the client-server communication is interrupted, such as network failures or system crashes. It helps ensure transaction consistency by enabling applications to safely retry transactions without risking duplicate work or data corruption.
In high-availability Oracle
environments—especially RAC, Data Guard, application servers, and distributed
systems—application failures during database outages can lead to transaction uncertainty.
This happens when a session is interrupted after an application issues a
COMMIT, but before it receives the commit confirmation.
In such cases the application cannot know
whether the transaction was committed, rolled back, or left incomplete. This
can lead to data inconsistency, duplicate transactions, or user-facing errors.
Oracle Transaction Guard (TG) guarantees that
an application can always determine the outcome of the last committed
transaction—even across node failures, session kills, network timeouts, or
planned maintenance.
Typical Transaction Guard Usage
The following pseudocode shows a typical usage of Transaction Guard:
Receive a FAN down event (or recoverable error)
FAN cancels the terminated session
If recoverable error (new OCI_ATTRIBUTE for OCI, isRecoverable for JDBC)
Get last LTXID from terminated session using getLTXID or from your callback
Obtain a new session
Call GET_LTXID_OUTCOME with last LTXID to obtain COMMITTED and USER_CALL_COMPLETED status
If COMMITTED and USER_CALL_COMPLETED
Then return result
ELSEIF COMMITTED and NOT USER_CALL_COMPLETED
Then return result with a warning (that details such as out binds or row count were not returned)
ELSEIF NOT COMMITTED
Cleanup and resubmit request, or return uncommitted result to the clientWithout Transaction Guard, an application that
loses its database connection during a COMMIT must re-execute the transaction
or attempt manual compensation. Both approaches risk:
Ø duplicate financial payments
Ø double bookings or orders
Ø lost or phantom commits
Ø data corruption in multi-tier apps
With Transaction Guard, Oracle ensures idempotent
and safe retry logic. Applications can confidently resume processing
without ambiguity.
Note: Avoiding duplication
in the payments systems can handle by another techniques or combination of them
such as in schema or SQL features, but in RAC env using TG is recommended.
How Transaction Guard Works
Transaction Guard uses a unique Global
Transaction ID (GTRID) associated with each transaction. When a transaction
completes, this GTRID and its outcome (committed or rolled back) are recorded
persistently. If the client loses connection before receiving the commit
confirmation, it can query the database with the GTRID to verify the exact
outcome.
Oracle supports Transaction Guard through the
Oracle Call Interface (OCI), JDBC, and other client APIs that are capable of
sending transaction outcome inquiries. The feature is transparent to the
database administrator but must be supported and utilized by the application or
middleware.
The Problem TG Solves:
In a 3-node Oracle RAC setup, if a node fails right after your application sends a `COMMIT`, your client receives an error. Without TG, you don’t know if the commit succeeded on the server before the connection broke. Retrying the transaction risks a duplicate entry, leading to logical data corruption.
How it Works: The Logical Transaction ID (LTXID)
If the connection fails during the commit, the
application saves the last LTXID.
Upon reconnection (which might be to a
different RAC node), the application uses the LTXID to call the server-side
procedure DBMS_APP_CONT.GET_LTXID_OUTCOME`.
The database returns the definitive outcome:
COMMITTED or NOT COMMITTED. No more guesswork!
This is essential for any application
leveraging the high-availability of a multi-node RAC cluster.
1. Creating the Service with `srvctl`
# 1. Add the service with COMMIT_OUTCOME=TRUE
--Consider load balancing based on
your env. below settings is a sample only.
srvctl add service -db RACDB -service TG_SERVICE -preferred racdb1,racdb2,racdb3 -commit_outcome TRUE -failovertype TRANSACTION -failovermethod BASIC -failoverretry 30 -failoverdelay 10
--or
srvctl add service -database codedb -service GOLD -preferred serv1 -available serv2 -commit_outcome TRUE -retention 604800
######JAVA Sample Code########
|
import
java.sql.CallableStatement; import
java.sql.Connection; import
java.sql.ResultSet; import
java.sql.SQLException; import
java.sql.Statement; import
oracle.jdbc.OracleConnection; import
oracle.jdbc.LogicalTransactionId; import
oracle.jdbc.pool.OracleDataSource; /** * Complete JDBC example demonstrating Oracle
Transaction Guard logic. * This example simulates a connection
failure during a commit and * uses the Logical Transaction ID (LTXID) to
check the transaction outcome * upon reconnection, ensuring at-most-once
execution. * * NOTE: This code is conceptual. To run it,
you must: * 1. Replace placeholders (USER, PASSWORD,
HOSTs, SERVICE_NAME) with actual values. * 2. Ensure the database service (e.g.,
'tg_service') is configured with COMMIT_OUTCOME=TRUE. * 3. Have the Oracle JDBC driver
(ojdbcX.jar) in your classpath. * 4. Have a table named 'TEST_TABLE' with a
column 'ID' (NUMBER) and 'DATA' (VARCHAR2). */ public
class TransactionGuardExample { // --- 1. RAC Connection Details
(Placeholders) --- private static final String USER =
"your_user"; private static final String PASSWORD =
"your_password"; private static final String SERVICE_NAME =
"TG_SERVICE"; // Must be configured with
COMMIT_OUTCOME=TRUE private static final String SCAN_IP =
"your_scan_ip"; // Placeholder for your Oracle SCAN Listener IP // Simplified RAC connection string using
SCAN Listener private static final String URL =
"jdbc:oracle:thin:@" + SCAN_IP + ":1521/" + SERVICE_NAME; // --- 2. Transaction Guard Outcome
Constants --- // These correspond to the output of
DBMS_APP_CONT.GET_LTXID_OUTCOME private static final int COMMITTED = 1; private static final int
USER_CALL_COMPLETED = 2; public static void main(String[] args) { LogicalTransactionId lastLtxid = null; Connection conn = null; try { // --- Initial Connection Setup
--- OracleDataSource ods = new
OracleDataSource(); ods.setURL(URL); ods.setUser(USER); ods.setPassword(PASSWORD); conn = ods.getConnection(); conn.setAutoCommit(false); System.out.println("Successfully
connected to the database."); // --- Transaction Attempt --- // 1. Get the LTXID *before* the
transaction starts lastLtxid = ((OracleConnection)
conn).getLogicalTransactionId(); System.out.println("LTXID
before transaction: " + lastLtxid); // 2. Execute the DML operation Statement stmt = conn.createStatement(); stmt.executeUpdate("INSERT
INTO TEST_TABLE (ID, DATA) VALUES (101, 'Transaction Guard Test')"); System.out.println("DML
executed successfully."); // 3. Attempt to commit System.out.println("Attempting
to commit..."); conn.commit(); System.out.println("Commit
successful (in a real scenario, this is where the failure would occur)."); // --- SIMULATED FAILURE POINT
--- // In a real-world scenario, the
network or a RAC node fails here. // The client sends the commit
but receives no confirmation. // We simulate this by forcing a
close and catching the subsequent error. // For this example, we'll just
proceed to the check logic as if a failure occurred. } catch (SQLException e) { // This catch block would
typically handle the connection failure System.err.println("Connection
failed during commit: " + e.getMessage()); // The lastLtxid is preserved for
the outcome check } finally { // Close the potentially broken
connection if (conn != null) { try { conn.close(); } catch
(SQLException e) { /* ignore */ } } } // --- 4. Outcome Check Logic (The
core of Transaction Guard) --- if (lastLtxid != null) { System.out.println("\n---
Reconnecting to check transaction outcome ---"); Connection newConn = null; try { // Re-establish connection (may connect
to a different RAC node) OracleDataSource ods = new
OracleDataSource(); ods.setURL(URL); ods.setUser(USER); ods.setPassword(PASSWORD); newConn = ods.getConnection(); // Call the server-side
procedure to check the outcome String sql = "{call
DBMS_APP_CONT.GET_LTXID_OUTCOME(?, ?, ?)}"; CallableStatement cstmt =
newConn.prepareCall(sql); // Input: The LTXID of the
uncertain transaction cstmt.setObject(1,
lastLtxid); // Output: The outcome
(COMMITTED or USER_CALL_COMPLETED) cstmt.registerOutParameter(2,
java.sql.Types.INTEGER); // Output: The status of the
user call (optional, but good practice) cstmt.registerOutParameter(3,
java.sql.Types.INTEGER); cstmt.execute(); int outcome = cstmt.getInt(2); int callStatus = cstmt.getInt(3); if (outcome == COMMITTED) { System.out.println("Outcome
Check: Transaction with LTXID " + lastLtxid + " was
COMMITTED."); } else if (outcome ==
USER_CALL_COMPLETED) { System.out.println("Outcome
Check: Transaction with LTXID " + lastLtxid + " was NOT COMMITTED (or
rolled back). User call completed."); } else { System.out.println("Outcome
Check: Outcome is UNKNOWN or an error occurred."); } // In a real application: // - If COMMITTED, do nothing
(transaction is done). // - If NOT COMMITTED, retry
the transaction with the new connection. } catch (SQLException e) { System.err.println("Error
during outcome check: " + e.getMessage()); } finally { if (newConn != null) { try { newConn.close(); } catch
(SQLException e) { /* ignore */ } } } } } } |
Key Points for RAC + Transaction Guard:
» Data Guard
» Multitenant including unplug/plug and online PDB relocate
Transaction Guard excludes failover across databases maintained by replication technology:
If you are using a database replica using any replication technology such as Golden Gate or Logical Standby or 3rd party replication, you cannot use Transaction Guard between the primary and the secondary databases in this configuration. You may use Transaction Guard within each database that participates in the replication.
Oracle Transaction Guard: XA Commit Outcome
Made Reliable
Starting with Oracle Database 12c Release 2,
Transaction Guard became smarter about handling XA transactions, especially in
environments using TP Monitors and distributed transaction coordinators.
Here’s the key behavior:
Ø Local transactions using autocommit
Ø Local transactions using explicit commits
Ø XA transactions committing with TMONEPHASE
>>> Transaction Guard provides a guaranteed commit outcome.
How
Oracle handles XA Two-Phase Commit
When an XA transaction uses TMTWOPHASE:
Ø Transaction Guard automatically disables
itself for that transaction
Ø A warning is returned if the API is
called
Ø The TP monitor is responsible for
commit outcome
Ø Transaction Guard is automatically re-enabled
for the next transaction
This design allows TP Monitors to return a definitive and unambiguous commit
outcome for TMONEPHASE operations, while respecting XA protocol rules for
distributed two-phase commits.
If you're building high-availability,
distributed, or RAC-based applications using XA, Transaction Guard gives you a
reliable way to avoid duplicate commits and ensures your application always
knows exactly what happened.
Summary:
Oracle Transaction Guard provides a robust
mechanism to achieve transaction outcome certainty in unreliable network
conditions. By leveraging unique transaction identifiers and persistent outcome
tracking, it allows applications to confidently handle failures and retries,
ensuring data integrity and consistent application behavior.
No comments:
Post a Comment