How the locking feature works with cursors

siddheart22 · New User Joined: 09 Oct 2006 Posts: 15 Location: Toronto

Hello,

I would like to understand how the locking feature works with cursors. Is it that the result set of the DECLARE statement is locked at the OPEN cursor statement or is it locked at the FETCH statement?

Thanks,

Gnanas SNG · Posted: Thu Mar 27, 2008 2:56 pm Post subject:

It depends on Isolation levels...

For instance, Cursor Stability(CS) locks any row on which the cursor is positioned during a unit of work. The lock on the row is held until the next row is fetched or the unit of work is terminated. If a row has been updated, the lock is held until the unit of work is terminated. A unit of work is terminated when either a COMMIT or ROLLBACK statement is executed.

More on...
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0509schuetz/

siddheart22 · New User Joined: 09 Oct 2006 Posts: 15 Location: Toronto

Thanks gnanas for responding.

I understand the concept of isolation levels, and lets say, for the purpose of this discussion, CS isolation level is used.
I would like to understand how this locking feature is applied in the case of Cursors.

Should the lock be applied when you OPEN the cursor (in which case, the entire result set from the DECLARE cursor should be locked) ? OR
Should the lock be applied when you FETCH (in which case lock would be applied only on the row fetched from the result set of DECLARE cursor)?

Thanks,

Gnanas SNG · Posted: Thu Mar 27, 2008 3:50 pm Post subject:

dbzTHEdinosauer · Posted: Thu Mar 27, 2008 4:20 pm Post subject:

siddheart22,

you have said, should the lock be applied...

are you planning to physically code a 'lock' yourself?

siddheart22 · New User Joined: 09 Oct 2006 Posts: 15 Location: Toronto

Thanks Gnanas again for a quick response.

I think you have misunderstood my question. My question is "where Db2 would apply locks would it be at the OPEN cursor level or would it be at the FETCH cursor level? "

I understand that CS would be the right isolation level to be used if we want a row level lock, but if Db2 is applying the locks at the OPEN cursor level than would it not be applying the locks at the entire result set from DECLARE cursor statement instead of an individual row?

enrico-sorichetti

no need to shout...
the question was clear, and kindly Gnanas provided You with a very good link, the first one,
if You had read it carefully You would have found
a very detailed explanation of what the different isolations levels imply

before posting this comment I went to check, here is the quote that should enlighten You

dick scherrer · Posted: Fri Mar 28, 2008 1:09 am Post subject:

Hello,