Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Cursor stability and locking

    Hi,
    using DB2 v9.5 on Linux.

    By default cursor stability isolation level is defined and used by select statements. What I am wondering how locking is really made. Lets see example:

    Code:
    SELLSID  BUYER_NAME  QUANTITY  
    1        buyer_no1        100           
    2        buyer_no2        110           
    3        buyer_no1        120
    Primary key is on sellsid column and no index on quantity or buyer_name.

    When I execute the following SQL:
    select buyer_name from qunatity_table where quantity = 100

    How is the locking made? There is IS lock on table, that is ok no questions on table locking. But what happens on rows locking?

    Because there is no index on quantity table database engine has to execute table scan, so all rows from the table has to be read.

    Now I am wondering how locking is made on rows if default cursor stability isolation level is used.

    I assume the row locking works like this, database makes reads per page and scans rows:
    - locks first row with S lock and read it
    - release S lock on first row and locks second row with S lock and read it
    - release S lock on second row and lock third row with S lock and read it
    - release S lock on third row.

    Is this correct?
    Regards
    Last edited by grofaty; 02-12-09 at 03:30.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, that's correct.

    What you have to keep in mind is that scanning over a result set is done with a cursor - always, even if you don't "see" a cursor in the application program. When you use a cursor, you do a FETCH and the cursor is positioned on a row. The row is S-locked before the FETCH returns. That S-lock is held until the next FETCH is executed. The lock must be held that long because you may have an UPDATE ... WHERE CURRENT OF ... and for that scenario DB2 has to guarantee that the row didn't change (or you would have a lost-update problem). That's the reason why the S-lock is not released right away as part of the first FETCH.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Hi,

    I would use the term "positioned" rather than FETCH as it is used in DB2 Documentation for CS:

    "Cursor Stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row.
    This lock remains in effect until the next row is fetched or the transaction is terminated.
    However, if any data on a row is changed, the lock must be held until the change is committed to the database."

    FETCH that we use in application is fetch accross the result set that could have been already built by OPEN:

    "In some cases, the result table of a cursor is derived during the execution of FETCH statements.
    In other cases, the temporary table method is used instead.
    With this method the entire result table is transferred to a temporary table during the execution of the OPEN statement."

    Example


    Code:
    create table blabla( id integer not null primary key, name varchar( 10 ) )
    insert into blabla values( 1, 'ann' )
    insert into blabla values( 2, 'cat' )
    commit
    Session 1
    =========

    Code:
    db2 (/SAMPLE) => select * from blabla
    
    ID          NAME
    ----------- ----------
              1 ann
              2 cat
    
      2 record(s) selected.
    Session 2
    =========

    Code:
    db2 (/SAMPLE) => select * from blabla
    
    ID          NAME
    ----------- ----------
              1 ann
              2 cat
    
      2 record(s) selected.
    
    db2 (/SAMPLE) =>
    Session 1
    =========

    Code:
    db2 (/SAMPLE) => update blabla set name = 'sam' where id = 2
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) =>
    Session 2
    =========

    Code:
    db2 (/SAMPLE) => declare c1 cursor for select * from blabla
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) => open c1
    it hangs BY OPEN

    Session 1
    =========

    Code:
    db2 (/SAMPLE) => update blabla set name = 'dad' where id = 1
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) => commit
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) =>
    The session 2 is released

    Session 1
    =========

    Code:
    db2 (/SAMPLE) => select * from blabla
    
    ID          NAME
    ----------- ----------
              1 dad
              2 sam
    
      2 record(s) selected.
    
    db2 (/SAMPLE) =>
    Session 2
    =========

    ok, session 2 is now released an we can fetch. The NAME in the record 1 has now value "dad" because session 1 has commited.

    Code:
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) => fetch c1
    
    ID          NAME
    ----------- ----------
              1 ann <-- it is the old value, it had been already read by open before the session 1 has changed it
    
      1 record(s) selected.
    
    db2 (/SAMPLE) => fetch c1
    
    ID          NAME
    ----------- ----------
              2 sam <-- it is the new value!
    
      1 record(s) selected.
    
    db2 (/SAMPLE) => select * from blabla
    
    ID          NAME
    ----------- ----------
              1 dad
              2 sam
    
      2 record(s) selected.
    
    db2 (/SAMPLE) =>

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    - locks first row with S lock and read it
    - release S lock on first row and locks second row with S lock and read it
    - release S lock on second row and lock third row with S lock and read it
    - release S lock on third row.
    This would mean that, while reading rows one or two, an other application could meanwhile have changed row three, before you get the chance to place an S lock on it. Is this really true?
    In that case, "inconsistent analysis" could happen:
    - Application A wants to find out how many buyers have quantity=100.
    - Application B updates quantities by interchanging quantities for sellsid=1 and sellsid=3.
    The answer to A's question is always "1", either before or after B's updates.
    But with the sketched locking scenario, the following could happen:
    - App.A places an S lock on row 1, reads the row, and sets its count to 1.
    - App.A releases the S lock, places an S lock on row 2 and reads it.
    - App.B places an X lock on rows 1 and 3, interchanges their quantities, and commits.
    - App.A waits for B to complete, then obtains an S lock on row 3, reads it and increments its count to 2.

    I thought that CS isolation guaranteed that this kind of inconsistent analysis is impossible. CS (for Application A) could guarantee this as follows:
    - App. A places an S lock on all three rows.
    - App. A reads row 1, sets its count to 1, releases the lock, reads row 2.
    - App. B X-locks row 1 but has to wait for the S-lock still sitting on row 3.
    - App. A releases row 2, reads row 3, releases row 3 and finishes with count=1.
    - App. B obtains an X lock on row 3 and does its work.

    So my question is: which of the two scenarios really is CS (for Application A)?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Hello Peter,

    Example for CS.

    Code:
    CREATE TABLE SALDO (ACCOUNT INTEGER NOT NULL, AMOUNT INTEGER, PRIMARY KEY(ACCOUNT) )
    
    INSERT INTO SALDO VALUES( 1, 20 )
    INSERT INTO SALDO VALUES( 2, 80 )
    
    COMMIT
    A customer has 2 accounts. ACC1 has 20 EUR, ACC2 has 80 EUR.

    Session 1 transfers money from ACC2 to ACC1, 10 EUR.
    Session 2 reads parallel account balance for ACC1, ACC2.

    Both sessions have CS (Cursor Stability).

    Code:
       Session 1
       ---------
    
    db2 (/SAMPLE) => select * from saldo
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          20
              2          80
    
      2 record(s) selected.
    
       Session 2
       ---------
    
    db2 (/SAMPLE) => select * from saldo
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          20
              2          80
    
      2 record(s) selected.
    
    
       Session 1
       ---------
    
    db2 (/SAMPLE) => update saldo set amount = amount - 10 where account = 2
    DB20000I  The SQL command completed successfully.
    
       Session 2
       ---------
    
    db2 (/SAMPLE) => declare curacc cursor for select * from saldo
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) => open curacc
    Session 2 hangs

    Code:
       Session 1
       ---------
    
    db2 (/SAMPLE) => update saldo set amount = amount + 10 where account = 1
    DB20000I  The SQL command completed successfully.
    db2 (/SAMPLE) => commit
    DB20000I  The SQL command completed successfully.
    Session 2 is released.

    Code:
       Session 2
       ---------
    
    db2 (/) => fetch curacc
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          20
    
      1 record(s) selected.
    
    db2 (/) => fetch curacc
    
    ACCOUNT     AMOUNT
    ----------- -----------
              2          70
    
      1 record(s) selected.
    
    db2 (/) => select * from saldo
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          30
              2          70
    
      2 record(s) selected.
    
    db2 (/) =>
    The cursor shows us balance ACC1=20 EUR and ACC2=70 EUR, although the customer has never had this balance.
    The correct balancies are (20,80) or (30,70).
    Balance (20, 70) is incorrect.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by shubin_du
    I would use the term "positioned" rather than FETCH as it is used in DB2 Documentation for CS:
    We are talking about the same thing. The execution of a FETCH statement causes the cursor to be repositioning. Therefore, the borders of such a repositioning operations are the begin/end of a FETCH statement and any locking activity occurs as part of that. That's why I was correctly referring to the FETCH statement to explain when is happening what with a lock.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose
    This would mean that, while reading rows one or two, an other application could meanwhile have changed row three, before you get the chance to place an S lock on it. Is this really true?
    In that case, "inconsistent analysis" could happen:
    - Application A wants to find out how many buyers have quantity=100.
    - Application B updates quantities by interchanging quantities for sellsid=1 and sellsid=3.
    The answer to A's question is always "1", either before or after B's updates.
    But with the sketched locking scenario, the following could happen:
    - App.A places an S lock on row 1, reads the row, and sets its count to 1.
    - App.A releases the S lock, places an S lock on row 2 and reads it.
    - App.B places an X lock on rows 1 and 3, interchanges their quantities, and commits.
    - App.A waits for B to complete, then obtains an S lock on row 3, reads it and increments its count to 2.

    I thought that CS isolation guaranteed that this kind of inconsistent analysis is impossible.
    No, CS only guarantees that you will not read uncommitted data. Once the data is committed, you read it right away. So the scenario you sketched above can happen and is valid with CS isolation level. What you need here is isolation level RS to prevent the situation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    What you need here is isolation level RS to prevent the situation.
    I know that RS places locks beforehand, but also keeps them until the end.
    A difference between RS and CS being the earlier *release* of locks by CS, I was unsure about the later *placing* of locks.
    Thanks for correcting my understanding on this matter!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by stolze
    We are talking about the same thing. The execution of a FETCH statement causes the cursor to be repositioning. Therefore, the borders of such a repositioning operations are the begin/end of a FETCH statement and any locking activity occurs as part of that. That's why I was correctly referring to the FETCH statement to explain when is happening what with a lock.
    Hi stolze,

    yes, we are talking about the same thing. I would like only to say that locking activity can occur behind the scene, by open. In this case our FETCH has no locking activity.

  10. #10
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by Peter.Vanroose
    I know that RS places locks beforehand, but also keeps them until the end.
    A difference between RS and CS being the earlier *release* of locks by CS, I was unsure about the later *placing* of locks.
    Thanks for correcting my understanding on this matter!
    RS holds the locks till the end of transaction and CS only till the end of record set fetch. This difference can be very important for the applicatinos with high degree of concurrency.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The isolation levels provide minimum guarantees on what must happen at least. The system may deviate from that and have longer locking periods, i.e. if DB2 were to hold locks in level CS in exactly the same manner as for RR, that is perfectly valid from the SQL standard's point of view. Or a DBMS could grab table-level X-locks on any activities and that in isolation level UR - it would conform to the standard. (It would also impact concurrency significantly, for sure.)
    Last edited by stolze; 02-16-09 at 10:48.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by shubin_du
    RS holds the locks till the end of transaction and CS only till the end of record set fetch.
    But you agree that RS obtains all locks before the first "row positioning" (or fetch), while CS obtains locks one by one, just before each single "row positioning" (or fetch) ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    The isolation levels provide minimum guarantees on what must happen at least.
    Sure.
    So the question is: "which minimal locking scenario does CS guarantee?"
    For which the answer apparently is:
    - obtain row locks, one by one, just before positioning on that row of data, and
    - release row locks, one by one, just before positioning on the next row of data.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Feb 2009
    Location
    Germany
    Posts
    23
    Quote Originally Posted by Peter.Vanroose
    But you agree that RS obtains all locks before the first "row positioning" (or fetch), while CS obtains locks one by one, just before each single "row positioning" (or fetch) ?
    Hi Peter,

    in my opininon it is not important if

    1. db2 obtains all locks before the first fetch and then fetches the locked records or
    2. it locks and fetches at the same time without releasing the fetched records

    Important is, that record is locked till the end of transaction and nobody can change this record after RS-Transaction has read it.

    RS doesn't try to restart query if it finds that the next record in the record set is locked by other transaction. It stops and waits, The records that are already locked at this record set by current RS transaction are not released. This can have unpleasant consequences for other transactions and can provoke deadlocks.

    Session 1

    Code:
    db2 (/SAMPLE) => select * from saldo
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          20
              2          80
    
      2 record(s) selected.
    Session 2

    Code:
    db2 (/) => set isolation RS
    DB20000I  The SQL command completed successfully.
    db2 (/) => select * from saldo
    
    ACCOUNT     AMOUNT
    ----------- -----------
              1          20
              2          80
    
      2 record(s) selected.
    
    db2 (/) => commit 
    DB20000I  The SQL command completed successfully.
    db2 (/) =>
    Session 1

    Code:
    db2 (/SAMPLE) => update saldo set amount = amount - 10 where account = 2
    DB20000I  The SQL command completed successfully.
    Session 2

    Code:
    db2 (/) => declare curacc cursor for select * from saldo
    DB20000I  The SQL command completed successfully.
    db2 (/) => open curacc amount 
    DB20000I  The SQL command completed successfully.
    Session 2 hangs. It has locked the record 1 but record 2 is already locked by session 1 therefore session 2 waits for session 1

    Session 1

    Code:
    db2 (/SAMPLE) => update saldo set amount = amount + 10 where account = 1
    Session 1 hangs as well. It try to lock record 1 but it is already locked by session 2.

    Deadlock.

    Code:
    SQL0911N  The current transaction has been rolled back because of a deadlock
    or timeout.  Reason code "2".  SQLSTATE=40001
    By CS we would have no deadlock because record 1 were released by session 2 just after fetching of record 1.

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by shubin_du
    in my opininon it is not important if
    1. db2 obtains all locks before the first fetch and then fetches the locked records or
    2. it locks and fetches at the same time without releasing the fetched records
    I believe that the moment when obtaining the lock *is* important.
    See the example in my previous post somewhere up here: http://www.dbforums.com/db2/1638223-...ml#post6381659
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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