Mahol Dot Org

Transaction Locking And Isolation In JDBC

Transaction is a set of instructions which follow atomicity,consistency,Integrity and durability.

1. Atomicity : This states that either all actions should complete or none should occur.

2. Consistency : This imposes certain business rule.

3. Integrity : This states that the two actions shall not interfere with each other or access each others data.

4. Durability : This states that the changes occurred due to a certain action or transaction must be stored in a durable persistent storage.

When we talk about the Transaction Locking And Isolation In JDBC, following are the issues we need to address when to concurrent transactions are taking place :

1. Dirty read : This happens when uncommitted data is read.Typical example is :

a. Transaction 1 inserts a row into a table.

b.Transaction 2 reads the new row.

c.Transaction 1 rolls back.

2. Non-repeatable read : This happens when data reads from the same table for the same row are not same.Typical example is

a. Transaction 1 reads a row.

b. Transaction 2 updates the row.

c. Transaction 1 reads the same row a second time and gets a different  results.

3. Phantom Read : This occurs due to appearing of new records when same query is run at different times.Typical example is :

a. Transaction 1 reads all rows that satisfy a WHERE clause.

b. Transaction 2 inserts additional rows that satisfies the WHERE clause.

c. Transaction 1 executes  the WHERE condition and picks up the additional row.

There are different transaction isolation levels used from JDBC coding to address these issues.The function to set isolation level is :

con.setTranscationIsolationLevel(intvalue);

Different integer values can be provided as follows :

0 – JDBC_TRANSACTION_NONE
1 – JDBC_TRANSACTION_READ_UNCOMMITTED
2 – JDBC_TRANSACTION_READ_COMMITTED
4 – JDBC_TRANSACTION_REPEATABLE_READ
8 – JDBC_TRANSACTION_SERIALIZABLE

Once we set the isolation levels using the above function,we can address the issues of transaction level in JDBC connection.As the isolation     level increases it gets stricter and lower level problems are also addressed.If we have set the value to 8 ,the isolation level with value lesser than 8 ,ie. 0,1,2,4, are also taken  care of.