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 client
Without 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)
Transaction Guard solves this uncertainty
using the Logical Transaction ID (LTXID).
Before a transaction, the JDBC driver
retrieves a unique LTXID from the database.
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.
JDBC Example for RAC:
I’ve put a small sample, working Java/JDBC
example that demonstrates the connection setup for a RAC (using a service
configured with `COMMIT_OUTCOME=TRUE`) and the crucial LTXID outcome check
logic.
Oracle Transaction Guard Setup for RAC
Enabling Oracle Transaction Guard (TG)
requires proper configuration of a database service using the `srvctl` utility
in your Oracle RAC environment.
1. Creating the Service with `srvctl`
To enable Transaction Guard, you must create a
service and set the `COMMIT_OUTCOME` property to `TRUE`. This service should be
used by all application clients that need to leverage TG.
Here is the complete `srvctl` command to
create a service named `TG_SERVICE` for a database named `RACDB` across your
three RAC nodes (`racdb1`, `racdb2`, `racdb3`):
# 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
--or
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('COMMIT_OUTCOME'):='true';
params('RETENTION_TIMEOUT'):=604800;
params('aq_ha_notifications'):='true';
dbms_service.modify_service('<service-name>',params);
END;
/
2. Start the service
srvctl
start service -db RACDB -service TG_SERVICE
3. Verify the service configuration
srvctl
config service -db RACDB -service
######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:
Ø Use Oracle JDBC Thin driver 12c or later.
Ø Enable Fast Connection Failover (FCF) and
Transaction Guard in the connection properties.
Ø Connect using SCAN (Single Client Access Name)
or a RAC-aware connect string with multiple nodes.
Ø Use
oracle.jdbc.OracleConnection.getTransactionOutcome() method after failure to
verify the final transaction status.
Ø Transaction Guard Coverage with Oracle
Database 12c & Newer
Ø You may use Transaction Guard on each database
in your system including restarting on and failing over between
Ø single instance database, Real Application
Clusters, Data Guard and Active Data Guard.
♨️Transaction Guard
is supported on Enterprise Edition and higher with the following Oracle
Database configurations:
» Single Instance Oracle RDBMS
» Real Application Clusters
» RAC One
» Data Guard
» Active Data Guard
» Multitenant including unplug/plug and online
PDB relocate
» Global Data Services for the above database
configurations
Transaction Guard supports the following transaction types against
Oracle Database 12c & newer:
» Local transactions
» DDL and DCL transactions
» Distributed and Remote transactions
» Parallel transactions
» Commit on Success (auto-commit)
» PL/SQL with embedded COMMIT
» Starting with Oracle Database 12c Release 2
(12.2.0.1), XA transactions using One Phase Optimizations including XA commit
flag TMONEPHASE and read optimizations
» ALTER SESSION SET Container with Service
clause, where the service uses Transaction Guard
Transaction Guard supports the following v12c & newer client
drivers:
» JDBC-Thin Driver
» OCI and OCCI client drivers
» ODP.NET, Managed Driver
» ODP.NET, Unmanaged Driver
Transaction Guard Exclusions
» Transaction Guard intentionally excludes
recursive transactions and autonomous transactions so that these can be
re-executed.
Transaction Guard for Oracle 12c Release 2 excludes:
» Active Data Guard with read/write DB Links
for forwarding transactions
» JDBC OCI is not supported, use JDBC thin
driver
» Two Phase XA transactions managed
externally. When using XA, Transaction Guard returns the commit outcome for one
phase XA transactions, and silently disables for externally-managed two-phase
as this outcome is owned by the TP monitor (see Transaction Guard with XA
Transactions)
» Transaction Guard cannot be used in the TAF
Callback for TAF or Application Continuity for OCI and ODP.NET, or in the JDBC
initialization callback for Application Continuity for Java.
TAF and Application Continuity (AC) are handling
Transaction Guard internally.
» Full database import cannot be executed with
Transaction Guard enabled. Use an admin service without Transaction Guard for
full database imports. User and object imports are not excluded.
Transaction Guard excludes failover
across databases maintained by replication technology:
Ø Replication to Golden Gate
Ø Replication to Logical Standby
Ø Third Party replication solutions
Ø PDB
clones clause (excluding PDB online relocation 12c Release 2 and newer)
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.
In this use case, each database must use a
different database identifier (DBID). Use V$DATABASE to obtain the DBID for each
database.
Transaction Guard with XA Transactions
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:
Supported
transaction types
Ø 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
Why this matters:
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.
***************************************
Alireza Kamrani
