Hi There,

In DB2 concurrence and isolation levels, there is cursor stability isolation level. What's the cursor in this isolation level? Is it the one created by DECLARE CURSOR statement, or the row which is being manipualted among the result set of a query or statement?

We know Enterprise Java Bean's transaction (EJB) isolation levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. DB2 has Repeatable Read, Read Stability, Cursor Stability, and Uncommitted Read respectively. Does every one know how to map these two systems when EJB works with DB2?

Have nice weekend,

Steven

Cursor stability does not just apply to when you open a cursor. If there are multiple rows that DB2 needs to read to get the answer set, all those rows are in the internal cursor and DB2 needs to obtain a Share (read) lock on each row. Using cursor stability means that when doing a select, DB2 will only hold a Share lock on the row being read, and not maintain that lock until the next commit (or other event that would cause all locks to be released). Maybe the same as Serializable, but I am not sure.

Thanks. The serializable in EJB is the highest (strictest) isolation level. When a transction uses serializable isolation, no other transactions are allowed to access the same tables. It is serialized.

In DB2 that would be an exclusive lock (X), which is different than the isolation level (which determines when read locks are released).

Can we map EJB isolation level to DB2 as following?

EJB --------------------- DB2
Read Uncommitted ---- Uncommitted Read
Read Committed ------ Cursor Stability
Repeatable Read ------ Read Stability
Serializable ------------ Repeatable Read

EJB
Read uncommitted can read uncommitted data (i.e., data changed by a different transaction that is still in progress). Dirty reads, nonrepeatable reads, and phanton reads can occur.

Read Committed cannot read uncommitted data; data that is being changed by a different transaction cannot be read. Dirty reads are prevented; nonrepeatable reads and phanton reads can occur.

Repeatable Read cannot change data that is being read by a different transaction. Dirty reads and nonrepeatable reads are prevented; phanton reads can occur.

Serializable has exclusive read and update privilege to data; different transactions can neither read nor write to the same data. Dirty reads, nonrepeatable reads and phanton reads are prevented. This isolation level is the most restrictive.

DB2
Uncommitted Read - When the Uncommitted Read isolation level is used, rows that are retrieved by a single transaction are only locked for the duration of that transaction if another transaction attempts to drop or alter the table from which the rows were retrieved. Because rows often remain unlocked when this isolation level is used, lost updates, dirty reads, nonrepeatable reads, and phantoms can occur.

Cursor Stability - When the Cursor Stability isolation level is used, each row that is referenced by a cursor being used by the isolating transaction is locked as long as the cursor is positioned on that row. The lock acquired remains in effect either until the cursor is repositioned (usually by calling the FETCH statement) or until the isolating transaction terminates. Thus, when this isolation level is used, SELECT statements that are issued more than once within the same transaction may not always yield the same results. Lost updates and dirty reads cannot occur; nonrepeatable reads and phantoms, however, can and may be seen.

Read Stability - When the Read Stability is used, all rows that are retrieved by a single transaction are locked for the duration of that transaction. Each row read by the isolating transaction cannot be changed by other transactions until the isolating transaction terminates. In addition, changes made to other rows by other transactions will not be seen by a transaction running under the Read Stability isolation level until they have been committed. Lost updates, dirty reads, and nonrepeatable reads cannot occur; phantoms can and may be seen.

Repeatable Read - When the Repeatable Read isolation level is used, all rows referenced by a single transaction are locked for the duration of that transaction. With this isolation level, any SELECT statement that is issued more than once within the same transaction will always yield the same results; lost updates, dirty reads, nonrepeatable reads, and phantoms cannot occur.

Does anybody there who works on building Websphere EJB component can tell whether the above map is correct or not?

got this from Developing Enterprise Java Applications Using DB2 Version 8 (db2aje80) redbook:

J2EE Isolation Level = DB2 Isolation Level
Serializable = Repeatable Read
- Prohibits dirty reads, nonrepeatable reads and phantom reads
Repeatable reads = Read stability
- Prohibits dirty reads and nonrepeatable reads, but it allows phantom reads
Read committed = Cursor stability
- Prohibits dirty reads, but allows nonrepeatable reads and phantom reads
Read uncommitted = Uncommitted read
- Allows dirty reads, nonrepeatable reads and phantom reads

That's a good answer.