Tuesday, July 5, 2011

Transactions in Jdbc

Transactions are a mechanism to group operations together so that either all of them complete together successfully or none of them do. This avoids database consistency problems that can occur if some groups of operations are only partly completed. Think of a bank transfer that requires withdrawing money from one account to deposit in another. If the withdraw is completed but the deposit fails then the customer is likely to be very unhappy. If the deposit succeeds but the withdraw fails then the bank is likely to be very unhappy. Actually, transactions handle other aspects of consistency as well. For example, ensuring that a second transaction sees the database as if either the first transaction has completely finished or as if it has not started yet but not as if some of the first transaction's operations have completed but not others - even if both transactions are running simultaneously.
When a Connection is obtained, by default its AutoCommit property is set to true. This means that every query execution is committed immediately after it is executed and before the next one is executed. To enable grouping of operations in transactions, you have to switch the AutoCommit property off:
con.setAutoCommit(false) ;
Now you have to obtain new statement objects from the connection (the old ones won't work), and query or update as usual. When all operations that you want to group together have completed, you must commit the updates to the database:
con.commit() ;
At this point you can continue with more operations which will be grouped into a new transaction or you can switch AutoCommit back on:
con.setAutoCommit(true) ;
If anything goes wrong during a transaction (e.g. an Exception is thrown or an error means that you cannot complete your group of operations) then you have to undo all operations in your transaction so far:
con.rollBack() ;

If the database or the machine crashes, rollBack will (essentially) be called for you automatically to clean up uncommitted transactions when the database is restarted.

You should make every effort to minimise the length of time that you have open transactions running (i.e. the time after the first operation in a transaction until the call of the commit or rollback that terminates it) as they hold expensive resources and, in particular, locks in the database system which may stop any other competing transactions from proceeding.
Getting all this working correctly requires careful attention to your Exception handling. You must embed a transaction in a try clause so that any exception will trigger a rollback. If you do have to handle an exception (and therefore rollback) in a method that normally closes open connections before returning, make sure that this does not create a loop hole that allows the method to return without closing the connection. You can use a finally block to ensure that this is handled correctly.
Finally, note that when you modify the AutoCommit status of a connection, all operations by any thread using that connection object are run in the same transaction. Therefore you have to be very careful about sharing connection objects between different threads (particularly important in servlet and JSP code). The simple rule is that you can share without problems a connection which has been set to auto commit. Do not share non auto committing connections unless you use some other mechanism to make sure that you don't end up merging different transactions into one - with consequences for committing and roll backs.

No comments:

Post a Comment