Cursor stability and locking

grofaty · #1 (**permalink**) 02-12-09, 02:23

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

stolze · #2 (**permalink**) 02-12-09, 06:43

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.

shubin_du · #3 (**permalink**) 02-14-09, 13: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) =>

Peter.Vanroose · #4 (**permalink**) 02-14-09, 14:27

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)?

shubin_du · #5 (**permalink**) 02-14-09, 18:51

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.

stolze · #6 (**permalink**) 02-16-09, 04:30

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.

stolze · #7 (**permalink**) 02-16-09, 04:37

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.

Peter.Vanroose · #8 (**permalink**) 02-16-09, 04:42

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!

shubin_du · #9 (**permalink**) 02-16-09, 07:33

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.

shubin_du · #10 (**permalink**) 02-16-09, 07:39

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.

stolze · #11 (**permalink**) 02-16-09, 09:45

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.)

Peter.Vanroose · #12 (**permalink**) 02-16-09, 12:16

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 · #13 (**permalink**) 02-16-09, 12:21

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.

shubin_du · #14 (**permalink**) 02-16-09, 18:31

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.

Peter.Vanroose · #15 (**permalink**) 02-17-09, 02:23

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: Cursor stability and locking