stored procedure lock question

[william.david.anderson]

Hi there,

I have a newbie question regarding stored procedures and locking.
I'm trying to use a stored
procedure to perform a 'select for update' and return a cursor.
Below is a stripped down version
of the procedure:

CREATE PROCEDURE SELBTFLFORUPDATE()
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT * FROM mfdp WHERE path = '/aNDERSNB/test/
file5'
FOR UPDATE;

OPEN C1;
END @
I'm able to access the cursor from the CLP or from my test CLI/ODBC
program, but the
locking isn't working like I would have thought. I have autocommit
off and have set my
isolation level to repeatable read for my clients. Yet, when I have
a client issue a call
to the stored procedure and then have another client issue a call
to the same procedure
before the first client has issued a commit, both calls to the
stored procedure complete.

In contrast, if I issue the 'select for update' command directly on
the client side
(e.g., with the CLP), the second 'select for update' hangs (as I
would expect) until the
first one is committed.

Am I doing something wrong here?

Thanks for any help,

Bill

[Serge Rielau]

wi********************@gmail.com wrote:

On Sep 18, 11:21 am, Serge Rielau <[e-mail]:

Serge,

Thanks for your reply.

As far as I can tell, even after I've fetched the rows (but
before closing the cursor or comitting the transaction),
the rows aren't locked. It almost seems like the 'FOR UPDATE'
clause when used in a stored procedure isn't locking rows
as it does when used in a CLI/ODBC statement or with
a CLP command.

When I explicitly use the 'FOR UPDATE' clause in my CLI program
and fetch the rows, they are locked at that point and another
client that tries to select the rows for update (when autocommit is
off and both clients have their isolation levels set to
repeatable read), hangs on its fetch call until the first one
commits
or rolls back.

When I use the 'FOR UPDATE' clause in a stored procedure
that the clients call, both sets of select and fetch calls return
before either has committed, suggesting that exclusive locks are
not being placed on the rows. Then, depending on the timing, I can
end up in a deadlock situation where one transaction is aborted
by DB2 due to the deadlock.

I'm not the one to claim user error easily, but that DB2 updates rows
without keeping an x-lock on it.... very unlikely.
Something in your story doesn't add up.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[william.david.anderson]

I'm probably not explaining the context very well, but
I did find that if I set the isolation level on the select
statement within the stored procedure (using the WITH
clause, e.g., "WITH RR" for repeatable read) that
everything works as expected.

Maybe the isolation level in stored procedures is not
necessarily the same as it is on the client side (e.g.,
just because it's set to RR on the client side may not
mean that it's enforced that way in a stored procedure
that's invoked by the client).

Anyway, thanks again for your comments.

Bill

I'm not the one to claim user error easily, but that DB2 updates rows
without keeping an x-lock on it.... very unlikely.
Something in your story doesn't add up.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mark A]

<wi********************@gmail.comwrote in message
news:11**********************@y27g2000pre.googlegr oups.com...

>

I'm probably not explaining the context very well, but
I did find that if I set the isolation level on the select
statement within the stored procedure (using the WITH
clause, e.g., "WITH RR" for repeatable read) that
everything works as expected.

Maybe the isolation level in stored procedures is not
necessarily the same as it is on the client side (e.g.,
just because it's set to RR on the client side may not
mean that it's enforced that way in a stored procedure
that's invoked by the client).

Anyway, thanks again for your comments.

Bill

Stored Procedures use static SQL which is bound into a package. The package
bind process has isolation level associated with it that has nothing to do
with the client.

[william.david.anderson]

Thanks for clarifying that. I mistakenly thought that the isolation
level
in a stored procedure would be the same as the level assigned to the
connection through which the call to the procedure is invoked.

>
Stored Procedures use static SQL which is bound into a package. The package
bind process has isolation level associated with it that has nothing to do
with the client.

[Mark A]

<wi********************@gmail.comwrote in message
news:11*********************@i38g2000prf.googlegro ups.com...

>
Thanks for clarifying that. I mistakenly thought that the isolation
level
in a stored procedure would be the same as the level assigned to the
connection through which the call to the procedure is invoked.

The whole point of a stored procedure is that it runs on the server, not the
client. Since it uses static SQL by default, the access plan is pre-compiled
into a package.