http://developer.mimer.com

Transaction Processing

---

Mimer SQL uses a method for transaction management called Optimistic Concurrency Control (OCC). OCC does not involve any locking of rows as such, and therefore cannot cause a deadlock.

JDBC Transactions

JDBC transactions are controlled through the Connection object. There are two modes for managing transactions within JDBC:

• auto-commit
• manual-commit.

The setAutoCommit method is used to switch between the two modes.

Auto-commit Mode

Auto-commit mode is the default transaction mode for JDBC. When a connection is made, it is in auto-commit mode until setAutoCommit is used to disable auto-commit.

In auto-commit mode each individual statement is automatically committed when it completes successfully, no explicit transaction management is necessary. However, the return code must still be checked, as it is possible for the implicit transaction to fail.

Manual-commit Mode

When auto-commit is disabled, i.e. manual-commit is set, all executed statements are included in the same transaction until it is explicitly completed.

When an application turns auto-commit off, a transaction is started. The transaction continues until either the commit or the rollback method is called; after that a new transaction is automatically started.

Calling the commit method ends the transaction. At that stage, Mimer SQL checks whether the transaction is valid and raises an exception if a conflict is identified.

If a conflict is encountered, the application determines how to continue, for example whether to automatically retry the transaction or inform the user of the failure. The application is notified about the conflict by an exception that must be caught and evaluated.

A request to rollback a transaction causes Mimer SQL to discard any changes made since the start of the transaction and to end the transaction.

Use the commit or rollback methods, rather than using the SQL COMMIT or ROLLBACK statements to complete transactions, for example:

 Statement stmt;
 int transactionAttempts;
 
 final int MAX_ATTEMPTS = 5; // Maximum transaction attempts
 
 // Open a connection
 url = "jdbc:mimer:/ExampleDB";
 con = DriverManager.getConnection(url, "MIMER_ADM", "admin");
 
 con.setAutoCommit(false); // Explicit transaction handling
 
 stmt = con.createStatement();
 
 // Loop until transaction successful (or max attempts exceeded)
 for (transactionAttempts = 1; ; transactionAttempts++) {
     // Perform a sequence of operations under transaction control
     stmt.executeUpdate("UPDATE mimer_store.currencies"
                      + "   SET exchange_rate = exchange_rate * 1.05"
                      + "   WHERE code = 'USD'"); 
 
     try {
         con.commit(); // Commit transaction
     
         System.out.println("Transaction successful");
         break;
     
     } catch(SQLException sqe) {
         // Check commit error - allow serialization failure
         if (sqe.getSQLState().equals("40001")) {
             // Check number of times the transaction has been attempted
             if (transactionAttempts >= MAX_ATTEMPTS) {
                 // Raise exception with application defined SQL state
                 throw new SQLException("Transaction failure", "UET01");
             }
         }
         else {
             // Raise all other exceptions to outer handler
             throw sqe;
         }
     } finally {
         con.close();
     }
 }

Setting the Transaction Isolation Level

The setTransactionIsolation method sets the transaction isolation level. The default isolation level for Mimer SQL is TRANSACTION_REPEATABLE_READ.

Note: With Enterprise Java Beans (EJB), the EJB environment provides the transaction management and therefore explicit transaction management is not required.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se