CS636 Class 11

Transactions

Need for (multistatement) transactions.

Currently we are using auto-commit – JDBC default.

Auto-commit means Commit each statement.

If we need to bundle statements together for a certain action, we need to turn off auto-commit & start using conn.commit() and conn.rollback().

conn.setAutoCommit(false);

 Concurrent access to DB without transaction can cause data problems

Standard ex: lost update: 2 concurrent processes both adding to an account, only one increment shows (R = read, W=write)

R old amt

Add delta1                          R old amt

W new amt                         Add delta2

                                           W new amt – overwrites other’s  update                

Ex: Suppose in pizza we had code for new PizzaTopping id: find max(id) in toppings, use id+1 in insert

2 admins adding a Topping can get same new id causing failure of one (PK violation)

R old ids                               R old ids

Add one                              add one

-------------------------------------------

               Same new ids

 In fact, the code in pizza1 uses the nextid from pizza_sys_tab to generate the next topping id. This approach can still have a problem without enough locking, but it's easier to fix because we only need to lock one row, not the whole table to prevent problems.

DB transactions were invented to solve these problems

Transactions, the deal:

Serializable transactions = textbook tx’s

Famous ACID properties:

Atomic – happen in whole or not at all (undo wipes out uncommitted changes)

Consistent – (if each tx code maintains a constraint, so will concurrent tx’s)

Isolation – each tx runs as if only one on system

Durable – once committed, data is held across system crash, disk crash

In real life, “isolation levels” defined by SQL standard

  4 isolation levels, 2 of which are common

Both are atomic and durable

Difference is in “isolation” At RC, tx’s data can be affected by another concurrent Tx

Serializable: DB in effect freezes (until commit/rollback) all data accessed (R or W) by a transaction, as seen from another transaction. This prevents other Tx’s from changing it (2 or more readers are allowed)

Read committed: DB freezes data changed by this Tx, as seen from another Tx. Data can be read by this transaction, then changed by another tx, thus changing the universe this tx runs in.

Note “freezes” is not a standard term, but I think it captures what’s happening.

Let’s look at the problems discussed above...

RC allows lost update unless special coding is used – “read for update” – reads with write locking

Or for SQL  update bal = bal + delta in a single update statement

-this is guarded by locks on the updated record, so other tx’s can’t intervene

RC uses much less locking, but has real problems

Transactions and UI

Tx’s use locks, which hold off other Tx’s progress, so Tx’s need to run quickly, say <= 50 ms < human response time

DB App Rule: no UI during a Tx. (in production)

Tx’s should be short, say less than 50 ms (milliseconds, 1/1000’s of seconds)

Transactions over time: UI takes much more time than properly short Tx’s, looks like this:

Timeline:


 ------------|-|----------------------------------------------------|-|---------------------------
  UI         Txn                     UI                             Txn

 

We see that sometimes the short Tx’s overlap in time (for different users), though not very commonly. We say they are concurrent. That’s when data problems can occur at read-committed isolation, and deadlocks can happen in Serializable.

 

Transactions in our layered System

Our setup has layers. The execution goes like this:

Presentation = UI … calls service API, calls DAO,

                                    returns to Service, [calls DAO, returns],

                                    returns to Presentation (more UI here)

So if the service layer method contains the Tx, we will be following the DB App Rule

(Note: if the DAO call contained the Tx, that would also satisfy the Rule, but would be overly restrictive.)

The service layer is in charge, so it defines what actions are bundled together into Tx’s

Conclusion: put commit/rollback in service layer

Sounds funny – we are putting all the SQL into the DAO and commit is a DB action…

But start & end Tx is more abstract than “select pid from T” in DAO

Conclusion: start and end Tx, in each single service layer call.  This is what we’ll do in pizza2 and music2.

Transactions

Pizza: has several DB-changing service API calls:

These all will be turned into transactions (serializable) in pizza2. Read-only actions also.

End of background on transactions, now turn to details of implementation.

JDBC

// Connection class has transaction methods. We take over on Tx’s by turning off auto-commit:

conn.setAutoCommit(false)

 Now no commits until we call commit

We also request serializable Tx’s: we override the default isolation level (almost certainly READ_COMMITTED)

conn.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE )

Looking forward: It’s not easy to get JPA to do this for us. With some application servers (for example Glassfish), we can just configure the “Connection Pool”, and this works with tomcat v7 or later with Oracle and mysql.

db actions for Tx, in JDBC:

conn.commit();       // or conn.rollback()

conn.setAutoCommit(true);    // We won’t do this, but other apps could

In pizza2, we are using JPA over JDBC, so we use its EntityTransaction objects. For an EntityTransaction tx, tx.begin(), tx.commit(), tx.rollback().

// See in Pizza2 DbDAO startTransaction, etc. Note that unlike JDBC, we need to explicitly start a transaction. In JDBC, as in embedded SQL in C, a transaction starts at the first use of database data.

If all goes well

The Tricky Part with  JDBC: Cleanly aborting a transaction

Some DB problem ? SQL exception

Ex. Insert fails. The DB changes we’ve made are now in an indeterminate state

So we have to do a rollback to get to a determined DB state

Further complication: JDBC rollback() can throw SQLException

Suppose something (like an insert) has thrown a SQLException e1.  We try a rollback and it throws SQLException e2.

What to do with e2???  It’s usually a lost connection. So we’ll discard e2 and preserve e1, hopefully a more useful indication of the real problem.  A rare case of justifiable exception-squelching.  (Not really appropriate to call it a cause. If we had a log, we could print its message there.)  End up with a mess, like this:

We looked at Transfer.java, now available in $cs636/jdbc along with JdbcCheckup.java.  This program turns off auto-commit, sets serializable isolation, and handles transfers between accounts. Note that the method doCustomerTransfers has presentation code, the method doTransfer works like a service-layer method, defining when transactions begin and end, and calling transfer to do the actual JDBC actions (other than commit and rollback), so transfer is like a DAO method.

If retries are not wanted, doTransfer can be simplified to just:

    public static void doTransfer(Connection conn, String fromAccount,
            String toAccount, double dollars) throws Exception {
         try {
                transfer(conn, fromAccount, toAccount, dollars);
                conn.commit();
                return; // success
            } catch (Exception e) {
                rollbackAfterException(conn);   // rollback, ignore any rollback exception
                throw e; // notify caller of problem
            }
     }

since the main handles Exceptions. In this case the customer transfers would be cut short by a deadlock or serialization abort. Adding back the retry code, these aborts are usually caused by transient conditions, so a retry can do the desired work and cause no problem to the presentation environment.

Note that money is represented by double here, no a good practice.

Here is another snippet, showing what we will be doing with the Exception: create a service-layer exception object

// starting with good Connection conn

          try {
            doDBWork(conn); // this can throw SQLException     
          }
          catch (SQLException e) {        
            try {
  	      conn.rollback();
            } catch (SQLException e2)
            {}  // secondary exception is not useful  
            throw new AppException(“DBWork failed”, e) // or otherwise fail this method
          } finally {
             conn.close();
          }
In pizza2, we are using JPA, but we still have the problem that rollback() can throw. To make the code look better than the above, I’ve put the core code in “rollbackAfterException”, along with explanation in comments. This gets called from various catch clauses. 

In pizza1, the DAO uses JDBC directly, so the DAO code throws SQLException when there’s a DB problem of any kind.

See pattern in service layer (aka business layer BL) in pizza1:  catch SQLException, throw Service Exception

In pizza2, the DAO throws various Exceptions—there’s no one superclass to collect them all together like JDBC’s SQLException, so we need to catch Exception to pick up all of them.

So the pattern there is:  catch Exception e1, throw ServiceException e

        Here, as in pizza1, e is created by new ServiceException( “ useful message”, e1)

 End up with a Service Exception, with cause e1, in the presentation layer code.

This means we can get e1 from e by e1 = e.getCause();

Note that getCause() is a method of Exception, so this is a general mechanism to retrieve the exception provided in the constructor of the Exception.

 SQL Exception as cause of ServiceException, in pizza1/pizza2: two objects with a ref between:




Of course there are no explicit transactions in pizza1, so we don’t see throws from rollback there

The presentation code can print out the useful message to the user, and, while we’re developing, the more internal SQL problem explained in e1’s message (available via toString(), i.e. by System.out.println(e1);)

See method exceptionReport(Exception e) in PizzaSystemConfig: it does e.getCause() and even e.getCause().getCause() to reach down two levels of causes.

HTTP:  read Chap. 18 to pg. 555.