JDBC: Introduction to JDBC (Part IV)-Transactions

1. Characteristic of Transaction:

1) A (Atomicity) --> All the operations in one transaction should be regarded as a whole. They cannot be seperated.

2) C (Consistency) --> 

3)  I  (Isolation)  --> One transaction should not interrupt or affect another transaction.

4) D (Durability)  --> All the changes that one transaction made to the DB should be persisted and cannot be rollback.

Comments: The characteristics of transaction are realized by DBMS.

 

2. JDBC Transaction Isolation levels:

1) TRANSACTION_NONE

2) TRANSACTION_READ_UNCOMMITTED

3) TRANSACTION_READ_COMMITTED

4) TRANSACTION_REPEATABLE_READ

5) TRANSACTION_SERIALIZABLE

Isolation Level	Transactions	Dirty Reads	Non-Repeatable Reads	Phantom Reads
TRANSACTION_NONE	Not supported	Not applicable	Not applicable	Not applicable
TRANSACTION_READ_COMMITTED	Supported	Prevented	Allowed	Allowed
TRANSACTION_READ_UNCOMMITTED	Supported	Allowed	Allowed	Allowed
TRANSACTION_REPEATABLE_READ	Supported	Prevented	Prevented	Allowed
TRANSACTION_SERIALIZABLE	Supported	Prevented	Prevented	Prevented

Comments:

    Usually, you do not need to do anything about the transaction isolation level, you can use the default one for your DBMS.

    The default transaction isolation level depends on your DBMS.

    JDBC allows you to find out what transaction isolation level your DBMS is set to.

connection.getTransactionIsolation();

 

3. Problems may occur during transaction:

1) Dirty Reads: --> That means we should not make changes to DB during transaction unless it is committed.

    Accessing an updated value that has not been committed is considered dirty-reads.

    Because it is possible for that value to be rolled back to its previous value.

    If you read a value that is later rolled back, you will have read an invalid value.

2) Non-Repeatable Reads:  --> That means we should lock a single ROW.

    Non-repeatable reads occurs when transaction A retrieves a row, transaction B subsequently updates the row.

    And transaction A later retrieves the same row again. <Remeber that right now transaction A hasn't finished yet.>

    Transaction A retrieves the same row twice but sees different data.

3) Phantom Reads:  --> That means we should lock a single TABLE.

    Phantom-reads occurs when transaction A retrieves a set of row satisfying a given condition,

    Transaction B subsequently insert/delete/update a row such that the row now meets the condition in transaction A.

    Transaction A later repeats the conditional retrieval, transaction A now sees a different result set compared with the previous one.

 

4. JDBC Transaction Usage

1) Set transaction isolation level:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

2) Disable auto-commit:

    JDBC connections start out with auto-commit mode enabled, where each SQL statement is implicitly demarcated with a transaction.

    User who wish to execute multiple statements per transaction must turn auto-commit mode off.

connection.setAutoCommit(false);

3) Commit transaction:

connection.commit();

4) Roll back transaction when exception occurs during transaction operation:

conn.rollback();

5) Restore auto-commit:

conn.setAutoCommit(true);

6) Restore transaction isolation level if necessary

int level = conn.getTransactionIsolation();
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
...
connection.setTransaxtionIsolation(level);

 

Reference Links:

1) http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html Oracle official docs

2) http://www.java2s.com/Tutorial/Java/0340__Database/JDBCTransactionIsolationLevels.htm Listed transaction isolation level in details