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:
Where the implementation of the actual database request would be something like this:
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.
Where the code for getTransactionOutcome is this:
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:
For a global service you have to use
gdsctl. On a standalone non-RAC database you can use
dbms_service like this:
Finally the DBMS_APP_CONT isn't executable by all users by default. To make it executable for user SCOTT:
Here is a video of my Transaction Guard demo: