Transaction Guard (TG) for Java – JDBC HA – part 5

Posted on , updated on by Yannick Jaquier
Oracle ,

Table of contents

Preamble

Transaction Guard (TG) is a database feature for applications to ensure that every transaction has been executed at most once in case of planned or unplanned outage. In the background each transaction is associated with a logical transaction ID that let you determine, afterward, if the transaction has committed and has been completed. Without Transaction Guard (TG) the typical example that is given is a transaction that is increasing by 5% the salary of all company employees. If the application fail, outside of the fact that you will make happy employees by giving them 10.25% you cannot rerun blindly the transaction from scratch. With Transaction Guard you can know from application layout how it went and present a warning or gray the submit button to avoid a second dramatic rerun.

As we have seen in part 1 you must use JDBC Thin driver to use this feature !

For testing I have created below test table:

SQL> CREATE TABLE test01 AS SELECT LEVEL AS id, 1 AS val FROM dual CONNECT BY LEVEL <= 10;
 
TABLE created.
 
SQL> DESC test01;
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VAL                                                NUMBER
 
SQL> SELECT * FROM test01;
 
        ID        VAL
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
 
10 ROWS selected.

The idea is to update each row using id column as a key and pause between the updates to let me kill the session and simulate an crash scenario. By default the Java connection has autocommit property set to TRUE

From Oracle Technology Network the requirements for TG are:

  • Use Oracle Database Release 12.1 or later.
  • Use an application service for all database work. Create the service using srvctl if using RAC or DBMS_SERVICE if not using RAC. You may also use GDSCTL.
  • Set the following properties on the service – COMMIT_OUTCOME = TRUE for Transaction Guard.
  • Grant execute permission on DBMS_APP_CONT package to the application user.
  • Increase DDL_LOCK_TIMEOUT if using Transaction Guard with DDL statements (for example, 10 seconds).

In part 1 of this series I have chosen only few options for my service and so I do not satisfy the minimum requirements for TG. I had to drop and recreate the service using the mandatory option. See issues encountered section for this.

The Oracle PL/SQL supplied package to know if a transaction has committed and has completed is DBMS_APP_CONT.GET_LTXID_OUTCOME:

SQL> DESC DBMS_APP_CONT
PROCEDURE GET_LTXID_OUTCOME
 Argument Name                  TYPE                    IN/OUT DEFAULT?
 ------------------------------ ----------------------- ------ --------
 CLIENT_LTXID                   RAW                     IN
 COMMITTED                      BOOLEAN                 OUT
 USER_CALL_COMPLETED            BOOLEAN                 OUT

This procedure has two Boolean out parameters and until recently it was not possible to map a PL/SQL Boolean in Java this is why all blog post (as well as official 12cR2 documentation) I have found, at the time of writing this post, are wrapping this procedure in a PL/SQL block to return numbers instead of Boolean. The limitation has gone with 12cR2:

JDBC Support for Binding PL/SQL BOOLEAN type

Starting from Oracle Database 12c Release 2 (12.2.0.1), Oracle JDBC drivers support binding PL/SQL BOOLEAN type, which is a true BOOLEAN type. PLSQL_BOOLEAN binds BOOLEAN type for input or output parameters when executing a PL/SQL function or procedure. With this feature, now JDBC supports the ability to bind PLSQL_BOOLEAN type into any PL/SQL block from Java.

Transaction Guard (TG) testing

Java testing code:

package tg01;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLRecoverableException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
 
import oracle.jdbc.LogicalTransactionId;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
 
public class tg01 {
  private static Boolean[] dbms_app_cont(Connection connection, LogicalTransactionId ltxid) throws SQLException {
    Boolean[] result;
    String query = "begin dbms_app_cont.get_ltxid_outcome(?, ?, ?); end;";
    CallableStatement callStmt1 = null;
 
    result = new Boolean[2];
    callStmt1 = connection.prepareCall(query);
    callStmt1.setObject(1, ltxid);
    callStmt1.registerOutParameter(2, OracleTypes.PLSQL_BOOLEAN);
    callStmt1.registerOutParameter(3, OracleTypes.PLSQL_BOOLEAN);
    callStmt1.execute();
 
    result[0] = callStmt1.getBoolean(2);
    result[1] = callStmt1.getBoolean(3);
    return result;
  }
 
  public static void main(String[] args) throws Exception {
    Connection connection1=null, connection2 = null;
    Statement statement1 = null;
    ResultSet resultset1 = null;
    OracleDataSource ods1 = new OracleDataSource();
    int i = 1;
    LogicalTransactionId ltxid = null;
    Boolean[] ltxid_result;
 
    ltxid_result = new Boolean[2];
 
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    }
    catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC Thin driver ?");
      e.printStackTrace();
      System.exit(1);
    }
 
    System.out.println("Oracle JDBC Driver Registered!");
 
    try {
      ods1.setUser("yjaquier");
      ods1.setPassword("secure_password");
      ods1.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-cluster-scan.domain.com)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=pdb1srv)))");
      connection1 = ods1.getConnection();
    }
    catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
    }
    System.out.println("Connected to Oracle database...");
    statement1 = connection1.createStatement();
    resultset1 = statement1.executeQuery("select sys_context('USERENV','SERVER_HOST') from dual");
    while (resultset1.next()) {
      System.out.println("Working on server " + resultset1.getString(1));
    }
 
    try {
      // lxtid must be taken "at right time"
      // If you take it after last update you might have committed returned value
      // equal to false. Means that all previous updates are not taken into account
      ltxid = ((OracleConnection)connection1).getLogicalTransactionId();
      for (i=1; i<=10; i++) {
        System.out.println("\nUpdate "+i+" at "+LocalDateTime.now().format(DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss")));          
        resultset1 = statement1.executeQuery("update test01 set val=val + 1 where id = " + i);
        // Sleeping 10 seconds to let me kill the session
        Thread.sleep(10000);
      }
    }
    catch (SQLRecoverableException recoverableException) {
      resultset1.close();
      connection1.close();
      try {
        connection2 = ods1.getConnection();
      }
      catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
      }
      ltxid_result = dbms_app_cont(connection2, ltxid);
      System.out.println("Committed: " +  ltxid_result[0] + ", user_call_completed: " + ltxid_result[1]);
      connection1 = connection2;
    }
  }
}

I start the execution of my Java program (only ojdbc8.jar must be added to your project) and after 4th update I kill the Oracle session:

tg01
tg01

My Java program tells us that the transaction has committed and that the user call has not completed.

If we check at database level:

SQL> SELECT * FROM test01;
 
        ID        VAL
---------- ----------
         1          2
         2          2
         3          2
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
 
10 ROWS selected.

As we see on above picture DBMS_APP_CONT.GET_LTXID_OUTCOME procedure tells us that committed has been done and user call has not completed. This is obviously confirmed by the state of my test01 table. We clearly understand that the program cannot be re-submitted blindly and a bit of work is mandatory to recover the situation…

As a side note the transaction has committed because the auto commit feature of the connection is TRUE by default, you can deactivate this with:

connection1.setAutoCommit(false);

And use an explicit commit at the end of the ten updates with:

connection1.commit();

If you kill the session after the ten updates and just before the commit (slight modification of above Java code) the Java program will display:>/p>

Committed: false, user_call_completed: false

In this situation you know the table is unchanged and that you can safely resubmit he operation…

Issues encountered

In my initial testing I received an error message when calling DBMS_APP_CONT.GET_LTXID_OUTCOME procedure:

ORA-14903: Corrupt logical TRANSACTION detected.
ORA-06512: AT "SYS.DBMS_APP_CONT", line 20
ORA-06512: AT "SYS.DBMS_APP_CONT", line 71
ORA-06512: AT line 1

This was simply coming, even if clearly written in documentation, from my service that was not satisfying the minimum requirement. So I had to drop and recreate my pdb1srv service that we created in part 1 of this series using the mandatory commit_outcome parameter (retention value I specify is in fact default value, 24 hours):

[oracle@server2 ~]$ srvctl STOP service -d orcl -service pdb1srv
[oracle@server2 ~]$ srvctl remove service -d orcl -service pdb1srv
[oracle@server2 ~]$ srvctl ADD service -db orcl -pdb pdb1 -service pdb1srv -notification TRUE -serverpool server_pool01 \
                    -failovertype TRANSACTION -commit_outcome TRUE -retention 86400
[oracle@server2 ~]$ srvctl START service -db orcl -service pdb1srv
[oracle@server2 ~]$ crsctl stat res ora.orcl.pdb1srv.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
CLUSTER Resources
--------------------------------------------------------------------------------
ora.orcl.pdb1srv.svc
      1        ONLINE  ONLINE       server2                  STABLE
      2        ONLINE  ONLINE       server3                  STABLE
--------------------------------------------------------------------------------

If later on you do not recall what you have configured for your service:

[oracle@server2 ~]$ srvctl config service -db orcl -service pdb1srv
Service name: pdb1srv
Server pool: server_pool01
Cardinality: UNIFORM
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Global: false
Commit Outcome: true
Failover type: TRANSACTION
Failover method:
TAF failover retries:
TAF failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: pdb1
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Service is individually enabled on nodes:
Service is individually disabled on nodes:
CSS critical: no

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>