Write recovery code with Transaction Guard

Write recovery code with Transaction Guard

When writing a servlet or any JDBC program, have you ever wondered how to recover from a database connection loss? A typical approach is to catch SQLRecoverableException exceptions and execute your recovery code. Such an exception is thrown by the driver when for example the socket is prematurely closed because of a temporary network outage, or if the database process has exited abnormally, etc. As the JavaDoc suggests, the first thing that usually happens in the recovery code is to close the already "dead" connection to cleanup resources in the driver, then open a new connection to the database, and finally re-execute the database request. The problem with this approach is that it may cause logical corruption by committing duplicate transactions. That's the problem Transaction Guard helps the developers solve.

This article is an introduction to Transaction Guard, a new 12c Oracle Database feature, which helps developers write stronger recovery code.

To illustrate the problem that Transaction Guard addresses, we will consider a basic code example. Let's assume we want to write a program that applies a 5% raise to all employees in the company. We'll use the EMP table to make things simple. Let's consider the following code snippet:

Connection jdbcConnection = getConnection(); boolean isJobDone = false; while(!isJobDone) { try { // apply the raise (DML + commit): giveRaiseToAllEmployees(jdbcConnection,5); // no exception, we consider the job as done: isJobDone = true; } catch (SQLRecoverableException recoverableException) { // if we get a SQLRecoverableException, we assume we can just retry the entire // transaction so we'll just loop while isJobDone becomes true. // Recovery first step is always to close the connection: try { jdbcConnection.close(); } catch(Exception ex) {} // ignore any exception // Now reconnect so that we can retry: jdbcConnection = getConnection(); } }

Where the implementation of the actual database request would be something like this:

void giveRaiseToAllEmployees(Connection conn, int percentage) throws SQLException { Statement stmt = null; try { stmt = conn.createStatement(); stmt.executeUpdate("UPDATE emp SET sal=sal+(sal*"+percentage+"/100)"); } catch (SQLException sqle ) { throw sqle; } finally { if(stmt != null) stmt.close(); } // At the end of the request we commit our changes: conn.commit(); }

There is nothing obviously wrong in this recovery code. The database request contains a single transaction and so it looks safe to retry when the driver throws a SQLRecoverableException during its execution. The bug in this code is that the commit() call shouldn't be part of the request because if that is the call that throws the SQLRecoverableException, then it isn't safe to retry without further safety checks. Think of the case where the commit call made it through to the database, the database successfully executed the transaction but the commit outcome did not reach the driver because the connection was lost. In such a case the driver would throw a SQLRecoverableException: No more data to read from socket but there is no need to retry anything. The code snippet above would execute the transaction twice.

This is where Transaction Guard is required to write a proper recovery code. Instead of blindly retrying the application can find out the outcome of the previous transaction and only retry if it wasn't committed.

Connection jdbcConnection = getConnection(); boolean isJobDone = false; while(!isJobDone) { try { // apply the raise (DML + commit): giveRaiseToAllEmployees(jdbcConnection,5); // no exception, we consider the job as done: isJobDone = true; } catch (SQLRecoverableException recoverableException) { // if we get a SQLRecoverableException, we assume we can just retry the entire // transaction so we'll just loop while isJobDone becomes true. // Recovery first step is always to close the connection: try { jdbcConnection.close(); } catch(Exception ex) {} // ignore any exception // Now reconnect so that we can retry: Connection newJDBCConnection = getConnection(); // This is where Transaction Guard becomes handy. Instead of blindly assuming that // the entire request failed because we got a SQLRecoverableException, we rely on // TG to verify the outcome of the previous attempt. // To use TG, we first need to retrieve the LogicalTranasactionId from the original // connection: LogicalTransactionId ltxid = ((OracleConnection)jdbcConnection).getLogicalTransactionId(); isJobDone = getTransactionOutcome(newJDBCConnection, ltxid); jdbcConnection = newJDBCConnection; } }

Where the code for getTransactionOutcome is this:

/** * GET_LTXID_OUTCOME_WRAPPER is a wrapper for DBMS_APP_CONT.GET_LTXID_OUTCOME which we * can't use directly because the JDBC thin driver doesn't support the PLSQL * BOOLEAN type. The wrapper uses a NUMBER type instead. */ private static final String GET_LTXID_OUTCOME_WRAPPER = "DECLARE PROCEDURE GET_LTXID_OUTCOME_WRAPPER("+ " ltxid IN RAW,"+ " is_committed OUT NUMBER ) "+ "IS " + " call_completed BOOLEAN; "+ " committed BOOLEAN; "+ "BEGIN "+ " DBMS_APP_CONT.GET_LTXID_OUTCOME(ltxid, committed, call_completed); "+ " if committed then is_committed := 1; else is_committed := 0; end if; "+ "END; "+ "BEGIN GET_LTXID_OUTCOME_WRAPPER(?,?); END;"; /** * Returns true if the LTXID committed or false otherwise. */ boolean getTransactionOutcome(Connection conn, LogicalTransactionId ltxid) throws SQLException { boolean committed = false; CallableStatement cstmt = null; try { cstmt = conn.prepareCall(GET_LTXID_OUTCOME_WRAPPER); cstmt.setObject(1, ltxid); // use this starting in 12.1.0.2 // cstmt.setBytes(1, ltxid.getBytes()); // use this in 12.1.0.1 (deprecated) cstmt.registerOutParameter(2, OracleTypes.BIT); cstmt.execute(); committed = cstmt.getBoolean(2); } catch (SQLException sqlexc) { throw sqlexc; } finally { if(cstmt != null) cstmt.close(); } return committed; }

In the recovery code if getTransactionOutcome returns true for the previous attempt then the Oracle Database guarantees that the previous transaction successfully committed and the application doesn't need to retry. If on the other hand, getTransactionOutcome returns false, then the Oracle Database guarantees that the previous attempt didn't commit and will not commit; hence it is safe to retry. Transaction Guard is a new feature of the 12C Oracle Database. To run the code examples above you will need the 12C Oracle JDBC Thin driver (download here).

The 12C JavaDoc for the JDBC Thin driver can be found here: JavaDoc.

On the server, Transaction Guard also needs to be turned on through the service. On a RAC database you must use srvctl. For example:

srvctl modify service -d orcl -service tgservice -commit_outcome true

For a global service you have to use gdsctl. On a standalone non-RAC database you can use dbms_service like this:

alter system set service_names='tgservice'; / declare params dbms_service.svc_parameter_array; begin params('COMMIT_OUTCOME') := 'TRUE'; dbms_service.modify_service('tgservice', params); end; / alter system register; /

Finally the DBMS_APP_CONT isn't executable by all users by default. To make it executable for user SCOTT:

GRANT EXECUTE ON DBMS_APP_CONT TO SCOTT;

Here is a video of my Transaction Guard demo:

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle Blogs Admin-Oracle

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today