Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    Unanswered: Cursor Stability isolation level

    1st Session:

    $ db2 values current isolation

    1
    --
    CS

    1 record(s) selected.

    $ db2 "select * from venkat"

    EMPID EMPNAME
    ----------- ------------------------------
    1 a
    2 b
    3 c
    4 d
    5 e

    5 record(s) selected.


    $ db2 "declare c1 cursor for select * from venkat"
    DB20000I The SQL command completed successfully.
    $ db2 +c open c1
    DB20000I The SQL command completed successfully.
    $ db2 +c fetch from c1

    EMPID EMPNAME
    ----------- ------------------------------
    1 a

    1 record(s) selected.

    ************************************************** ********************************************
    2nd Session:

    $ db2 values current isolation

    1
    --
    CS

    1 record(s) selected.


    $ db2 "update venkat set empname='2a' where empid=1"
    DB20000I The SQL command completed successfully.


    As you can see i was able to update the row where empid=1, in Session 2 ,which is currently being fetched in Session 1.

    My Question:
    As per the Cursor Stability isolation level, the row where empid=1 which is currently being fetched in Session 1 with CS isolation level should not be updatable by Session 2 (or other application). So why i am able to update the fetched row in session 1 from session 2?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The lock held on the row during FETCH servers only one purpose: to support a positioned-update through cursor C1.

    However, your cursor is not declared with the FOR UPDATE clause, so DB2 assumes a read-only cursor and then it doesn't have to hold any lock. If you use the FOR UPDATE clause, things work exactly as you expect, i.e. the UPDATE statement in session 2 will be blocked.

    Fetching the record again using a scrollable cursor would require isolation level RS to get the exact same row. CS wouldn't guarantee that 2x fetching the same row will give the exact same values.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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