Transaction guard

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application.

In 11g and below, your java code use to look like


try {
  insert into...
} catch () {
  error_handling()
}

but one probably assumed the insert failed when it was committed (e.g. database server process core dump).

Now in 12c, you can get a logical transaction id and then later, from another session, check if that transaction was committed. Which solves quite a bunch of integrity issues (e.g. duplicate rows)

Let’s try


import java.sql.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;

public class TG {
  public static void main(String argv[]) throws
      SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION"
      +"=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)("
      +"Port=1521))(CONNECT_DATA=(SERVICE_NAME="
      +"svc01)))";
    OracleDataSource ods=new OracleDataSource();
    ods.setURL(url);
    ods.setUser("SCOTT");
    ods.setPassword("tiger");
    OracleConnection conn = (OracleConnection) 
      ods.getConnection();
    LogicalTransactionId ltxid = conn.
      getLogicalTransactionId();
    try {
      System.out.println("Start");
      conn.prepareStatement(
        "insert into t values (1)").execute();
      if (Math.random() > .5) {
        throw new Exception();
      }
      System.out.println("OK");
    } catch (Exception e) {
      System.out.println("ERROR");
      OracleConnection conn2 = 
        (OracleConnection) ods.getConnection();
      CallableStatement c = conn2.prepareCall(
        "declare b1 boolean; b2 boolean; begin " 
        +"DBMS_APP_CONT.GET_LTXID_OUTCOME(?,b1,"
        +"b2); ? := case when B1 then "
        +"'COMMITTED' else 'UNCOMMITTED' end; "
        +"end;");
      c.setBytes(1, ltxid.getBytes());
      c.registerOutParameter(2, 
        OracleTypes.VARCHAR);
      c.execute();
      System.out.println("Status = "+
        c.getString(2));
    }
  }
}

getLogicalTransactionId gives me a transaction id (this is internally saved in SYS.LTXID_TRANS so it survives reboots, failover and disconnections) and GET_LTXID_OUTCOME gets the outcome.

There is few preparation steps


GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT;
declare PARAMETER_ARRAY dbms_service.
  svc_parameter_array; 
begin 
  PARAMETER_ARRAY('COMMIT_OUTCOME'):='true';
  dbms_service.create_service(
    'SVC01','TNS01',PARAMETER_ARRAY); 
  dbms_service.start_service('SVC01'); 
end;
/
CREATE TABLE SCOTT.T(x number);

Due to my Random() call, I get exceptions sometimes, but it is always commits


C:\> java TG
Start
OK

C:\> java TG
Start
ERROR
Status = COMMITTED

C:\> java TG
Start
ERROR
Status = COMMITTED

No need to redo the insert.

Now I dropped the table t and run the same code


SQL> drop table scott.t;

Table dropped.

C:\>java TG
Start
ERROR
Status = UNCOMMITTED

Now it fails and I know it!

Published by

Laurent Schneider

Oracle Certified Master

3 thoughts on “Transaction guard”

  1. Hi
    I was running along your java code, However, the following error occurred.
    Ask for help.

    
    ERROR
    Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 51:
    PLS-00103: Encountered the symbol "." when expecting one of the following:
    
       constant exception <an identifier>
       <a double-quoted delimited-identifier> table long double ref
       char time timestamp interval date binary national character
       nchar
    The symbol "<an identifier>" was substituted for "." to continue.
    ORA-06550: line 1, column 81:
    PLS-00103: Encountered the symbol "" when expecting one of the following:
    
       begin function pragma procedure subtype type <an identifier>
       <a double-quoted delimited-identifier> current cursor delete
       exists prior
    The symbol "begin" was substituted for "" to continue.
    

  2. Hi

    This my error. I cut my long lines to make it mobile-phone friendly but I forget a space after the BEGIN.

    Please try with begin " with the additional space. The original post is corrected.

    Thanks for the notification

Leave a Reply