Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    61

    Question Unanswered: What's a cursor in the cursor stability isolation level?

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    61
    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.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 that would be an exclusive lock (X), which is different than the isolation level (which determines when read locks are released).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2004
    Posts
    61
    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?

  6. #6
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    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
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  7. #7
    Join Date
    Mar 2004
    Posts
    61

    Talking Thanks!

    That's a good answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •