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!

3 thoughts on “Transaction guard

  1. Pingback: dotNet transaction guard | Laurent Schneider

  2. Hyunho.Jeong

    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
    table long double ref
    char time timestamp interval date binary national character
    nchar
    The symbol "" 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
    current cursor delete
    exists prior
    The symbol "begin" was substituted for "" to continue.

  3. Laurent Schneider Post author

    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

Comments are closed.