Overview
The ANSI/ISO SQL standard defines four levels of transaction isolation in terms of three potential problems that must be prevented between concurrent transactions. These problems are:
-
Dirty Read - A transaction reads data written by another concurrent uncommitted transaction.
-
Non-Repeatable Reads - A transaction re-reads data it has previously read and finds that data has been modified by another transaction (one that has been committed since the transaction's previous read).
-
Phantom Read - A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
Transaction Isolation Levels
The four levels of transaction isolation are:
-
Uncommitted Read
-
Committed Read
-
Repeatable Read
-
Serializable.
The isolation level that your transaction runs in determines how sensitive your application is to changes other users' transactions make, and consequently, how long your transaction must hold locks to protect against these changes. The ANSI SQL standard defines four levels of transaction isolation.
Uncommitted Read
Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, regardless of whether or not the data has been committed. For example, another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data. Your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back their transaction, so logically, those changes never occurred.
Committed Read
Committed Read ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.
Repeatable Read
If you want the read operations to be repeatable, choose the third isolation level. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction will not pick up any changes to data values that another user's transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.
Serializable
The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you will not see phantoms if the same query is issued twice within a transaction.
Transaction isolation Levels Behavior
The four transaction isolation levels and the corresponding behaviors are described below:
|
Isolation Level
|
Dirty Read
|
Non-Repeatable Read
|
Phantom Read
|
|
Read Uncommitted
|
Possible
|
Possible
|
Possible
|
|
Read Committed
|
Not possible
|
Possible
|
Possible
|
|
Repeatable Read
|
Not possible
|
Not possible
|
Possible
|
|
Serializable
|
Not possible
|
Not possible
|
Not possible
|
Support for Transaction Isolation Levels in Orbix E2A Application Server
With Orbix E2A Application Server, you can specify the transaction isolation level for an entire EJB in an application's cc.xml file by setting the new <connection-tx-isolation-level> element in the relevant <resource-ref> and <cmp-datasource> elements.
The valid values for the <connection-tx-isolation-level> element are:
-
TRANSACTION_READ_UNCOMMITTED
-
TRANSACTION_READ_COMMITTED
-
TRANSACTION_REPEATABLE_READ
-
TRANSACTION_SERIALIZABLE
The following is an extract of a cc.xml file using transaction isolation levels for the Account and Person demos that ship with Orbix E2A Application Server.
|
<configuration>
<enterprise-beans>
<entity>
<ejb-name>Account</ejb-name>
<jndi-name>iona/ipas/simple/Account</jndi-name>
<jndi-source-name>CosNaming</jndi-source-name>
<resource-ref>
<res-ref-name>jdbc/Accounts</res-ref-name>
<res-ref-link>JDBCAccounts</res-ref-link>
<connection-tx-isolation-level>TRANSACTION_READ_COMMITTED
</connection-tx-isolation-level>
</resource-ref>
</entity>
<entity>
<ejb-name>Person</ejb-name>
<jndi-name>iona/ipas/simple/Person</jndi-name>
<jndi-source-name>CosNaming</jndi-source-name>
<cmp-datasource>
<res-ref-name>jdbc/Person</res-ref-name>
<res-ref-link>JDBCPerson</res-ref-link>
<connection-tx-isolation-level>TRANSACTION_SERIALIZABLE
</connection-tx-isolation-level>
</cmp-datasource>
...
</entity>
</enterprise-beans>
<resources>
<resource>
<resource-name>JDBCPerson</resource-name>
<jndi-name>iona:cloudscape/demos</jndi-name>
</resource>
<resource>
<resource-name>JDBCAccounts</resource-name>
|
|
<jndi-name>iona:cloudscape/demos</jndi-name>
</resource>
</resources>
...
</configuration>
|