Transaction isolation levels, consenquences and configuration

1.     Introduction

 In this post I sum up the different isolation levels and how we can configure them in JBoss AS depending on the type of datasource defined for our JEE application.

2.     JDBC transaction isolation levels

Transaction isolation levels specify what data is visible to statements within a transaction. These levels directly impact the level of concurrent access by defining what interaction is possible between transactions against the same target data source.

READ UNCOMMITTED: allows a row changed by one transaction to be read by another transaction before any changes in that row have been committed (a "dirty read"). If any of the changes are rolled back, the second transaction will have retrieved an invalid row.

READ COMMITTED: prohibits a transaction from reading a row with uncommitted changes in it. This is the default level for most of databases.

REPEATABLE READ: prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read").

SERIALIZABLE: includes the prohibitions in TRANSACTION_REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.

NONE: transactions are not supported.

Depending on your isolation level we can have three phenomena:

Dirty reads

1.     Transaction A inserts a row into a table.

2.     Transaction B reads the new row.

3.     Transaction A rolls back.

4.     Transaction B may have done work to the system based on the row inserted by transaction A, but that row never became a permanent part of the database.

Nonrepeatable reads

1.     Transaction A reads a row.

2.     Transaction B changes the row.

3.     Transaction A reads the same row a second time and gets the new results.

Phantom reads

1.     Transaction A reads all rows that satisfy a WHERE clause on an SQL query.

2.     Transaction B inserts an additional row that satisfies the WHERE clause.

3.     Transaction A re-evaluates the WHERE condition and picks up the additional row.

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
READ UNCOMMITTED	Permitted	Permitted	Permitted
READ COMMITTED	No Permitted	Permitted	Permitted
REPEATABLE READ	No Permitted	No Permitted	Permitted
SERIALIZABLE	No Permitted	No Permitted	No Permitted

3.     How to set transaction isolation level in JBoss AS 4.2.2

 As far as I know we can configure it within server/my_server/deploy/*-ds.xml (i.e oracle-ds.xml, mysql-ds.xml).

There are two types of datasources where we can define it:

local-tx-datasource: identifies a datasource that uses single phase commit transactions, therefore there is just one resource per JTA transaction (local transaction).

xa-tx-datasource: identifies a datasource that uses two phase commit transactions, therefore there are more than one resource per JTA transaction (global transaction).

Note: Use xa-tx-datasource when it is really needed. It is expensive in terms of performance.

The element to set is  

transaction-isolation: specifies the java.sql.Connection transaction isolation level to use. The constants defined in the Connection interface are the possible element content values and include:

TRANSACTION_READ_UNCOMMITTED

            TRANSACTION_READ_COMMITTED

TRANSACTION_REPEATABLE_READ

TRANSACTION_SERIALIZABLE

TRANSACTION_NONE

We can find further information in this link JBoss web site