Friday, September 9, 2011

JDBC 'commit' even for SELECT

You probably knew this, but a 'commit' can even be necessary for SELECT calls that don't change any data.

After calling JDBC Connection.setAutoCommit(true)), every JDBC call, be it SELECT, INSERT, UPDATE, DELETE starts a transaction that needs to be ended with a commit (or rollback).

This might be obvious for a sequence of INSERT or UPDATE calls that change data, and you want a final commit() to perform an atomic update.

With a SELECT, the update is necessary to avoid the following situation:
  1. Calling SELECT to fetch something. That starts a Transaction!
  2. Now somebody else changes the data.
  3. You call SELECT again to get the 'latest', but since you're still in the original Transaction, you get the same old data as in step 1.
If you have other code that changes the data and happens to call commit() before you reach step 3, all will look OK, but to be sure that you end the original reading transaction and always get the 'latest' data in a later read, it has to be done like this:
  1. SELECT, commit()
  2. Now the data might change.
  3. If you later need to fetch the 'latest', again SELECT and commit()

Tuesday, September 6, 2011

RAOTA - What an idea!

Rest Areas on the Air, starting on Nov. 23, 2011:

Map http://www.tdot.state.tn.us/maintenance/restareas.htm mentions "2 hour parking limit":