eWebProgrammer eweb





JDBC 

Performing Transactions

Transaction Methods of the Connection Interface

A transaction is a set of SQL operations that needs to be either executed all successfully or not at all. Failure to perform even one operation leads to an inconsistent and erroneous database. A database must satisfy the ACID properties (Atomicity, Consistency, Isolation, and Durability) to guarantee the success of a database transaction.

ACID

  1. Atomicity: Each transaction should be carried out in its entirety; if one part of the transaction fails, then the whole transaction fails.
  2. Consistency: The database should be in a valid state before and after the performed transaction.
  3. Isolation: Each transaction should execute in complete isolation without knowing the existence of other transactions.
  4. Durability: Once the transaction is complete, the changes made by the transaction are permanent (even in the occurrence of unusual events such as power loss).
Classic Example
A classic example of a transaction is a fund transfer through a bank account. If one wants to transfer some money ($100.00) to another account, the money should be deducted from the first account and added to the second account. There are two operations to complete the fund transfer (which we will call a transaction).
It is not acceptable if either operation fails: If money is deducted from the first account and not added to the second account, the first account holder unnecessarily loses $100; if the second account gets an amount of $100 without deducting from the first account, the bank will definitely have a problem. Hence, either both operations are successful or both operations fail.



Transaction-Related Methods in the Connection Interface

Method Description
void setAutoCommit(boolean autoCommit) Sets the auto-commit mode to true or false. By default, Connection objects have auto-commit set to true, and you can set it to false by calling this method with false as the argument value.
boolean getAutoCommit() Returns the auto-commit mode value (a true value means auto-commit mode, and a false value means manual commit mode).
Savepoint setSavepoint() Creates a Savepoint object in the current transaction and returns that object.
Savepoint setSavepoint(String name) Same as the previous method, except that the Savepoint object has a name associated with it.
void releaseSavepoint(Savepoint savepoint) Removes the given Savepoint object and the subsequent Savepoint objects from the current transaction.
void rollback(Savepoint savepoint) Rolls back to the given Savepoint state. In other words, all the changes done after the Savepoint was created will be lost or removed (an undo operation till that Savepoint). Will throw a SQLException if rollback cannot be done (for example, an invalid Savepoint object is passed).
void rollback() Rolls back (undoes) all the changes made in the current transaction. Will throw a SQLException if rollback fails (e.g., rollback() was called when auto-commit mode is set).
void commit() Makes (commits) all the changes done so far in the transaction to the database.