MVSFORUMS.com

Luc Orient · Posted: Sat Aug 25, 2007 9:14 am Post subject: SELECT FOR UPDATE and locks

Hello :
A question about duration of a lock in a specific case :

When I have a "SELECT FOR UPDATE" cursor and a package bound with ISOLATION(CS) "a row or page lock is held only long enough to allow the cursor to move to another row or page" (source = DB2 Administration Guide).

My question :
If the package CLOSE the cursor, is the lock released after this statement ?

Thank's in advance ...
Fran

kolusu · Posted: Sat Aug 25, 2007 12:15 pm Post subject:

Luc Orient,

When you close a cursor with the CLOSE CURSOR statement that includes the WITH RELEASE clause, the database manager attempts to release all read locks that have been held for the cursor. Table read locks are IS, S, and U table locks. Row-read locks are S, NS, and U row locks. Block-read locks are IS, S, and U block locks.

The WITH RELEASE clause has no effect on cursors that are operating under the CS or UR isolation levels. When specified for cursors that are operating under the RS or RR isolation levels, the WITH RELEASE clause ends some of the guarantees of those isolation levels. Specifically, a RS cursor may experience the nonrepeatable read phenomenon, and a RR cursor may experience either the nonrepeatable read or phantom read phenomenon.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

dbzTHEdinosauer · Posted: Sat Aug 25, 2007 12:54 pm Post subject:

Kolusu,

If there has been an 'UPDATE CURRENT CURSOR', will not a COMMIT or ROLLBACK be required to release any locks on updated rows/pages???
_________________
Dick Brenholtz
American living in Varel, Germany

Luc Orient · Posted: Sat Aug 25, 2007 1:43 pm Post subject:

Luc Orient · Posted: Sat Aug 25, 2007 1:51 pm Post subject:

dbzTHEdinosauer · Posted: Sat Aug 25, 2007 2:38 pm Post subject:

Luc,

If you are not going to UPDATE or DELETE anything why are you using the FOR UPDATE OF clause?
Are you using the FOR UPDATE OF clause just to lock pages?
What is the goal of your SQL?
_________________
Dick Brenholtz
American living in Varel, Germany

Luc Orient · Posted: Sat Aug 25, 2007 3:34 pm Post subject:

dbzTHEdinosauer · Posted: Sat Aug 25, 2007 4:42 pm Post subject:

you should look at the BIND parms, especially the RELEASE(COMMIT) & RELEASE(DEALLOCATE) parms.

What I am getting at is there is more to it than just the SQL. The Bind parms as well as the locks setting - page, row, etc (Database setup)... have a lot to do with what is going on. In addition, a lot has to do with which version of DB2 you are using.
_________________
Dick Brenholtz
American living in Varel, Germany