error trapping in select

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dcruncher4@aim.com - 19 Jan 2007 21:54 GMT

I have a condition in which I have to ignore an error while SELECTING.
The error is lock timeout (Db2 error code -911). When fetchrow_arrayref

fetches the locked row, I do get -911 which I trap in my code. When I
move on to the next row doing another fetchrow_arrayref, I get error -3
(no statement executing). Does that mean that fetchrow_arrayref
will stop processing at the first error and there is no way to continue
to the next stop, ignoring it.

DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

thanks.

Reply to this Message

Mark A - 19 Jan 2007 22:15 GMT

>I have a condition in which I have to ignore an error while SELECTING.
> The error is lock timeout (Db2 error code -911). When fetchrow_arrayref
[quoted text clipped - 9 lines]
>
> thanks.

You can append WITH UR on the end of the select to not take any locks, and
to ignore all locks taken by others.

There are some slightly less drastic measures that can be used to minimize
lock contention, but not completely eliminate it like you can by using WITH
UR.

Reply to this Message

dcruncher4@aim.com - 19 Jan 2007 22:42 GMT

> You can append WITH UR on the end of the select to not take any locks, and
> to ignore all locks taken by others.
>
> There are some slightly less drastic measures that can be used to minimize
> lock contention, but not completely eliminate it like you can by using WITH
> UR.

I am aware of this, but I can't use this if I end up with
a phantom row (that is a row which I read but
rolled back subsequently). That is a BIG no in my
case.

Is there a way to get an indication from DB2 that
I am reading an uncomitted row. If I can get that,
then I can store it for the time being and then
revisit later to double check.

Reply to this Message

Mark A - 19 Jan 2007 23:16 GMT

> I am aware of this, but I can't use this if I end up with
> a phantom row (that is a row which I read but
[quoted text clipped - 5 lines]
> then I can store it for the time being and then
> revisit later to double check.

I think you can use
SET CURRENT LOCK TIMEOUT 0
to immediately get -911 back if the row is locked, but to actually see the
data, you will need to issue another SELECT and use WITH UR.

Reply to this Message

dcruncher4@aim.com - 19 Jan 2007 23:38 GMT

> I think you can use
> SET CURRENT LOCK TIMEOUT 0
> to immediately get -911 back if the row is locked, but to actually see the
> data, you will need to issue another SELECT and use WITH UR.

after -911 error fetchrow_arrayref stops further processing.

Reply to this Message

Knut Stolze - 21 Jan 2007 18:46 GMT

> I have a condition in which I have to ignore an error while SELECTING.
> The error is lock timeout (Db2 error code -911).

Could you provide the complete error message? SQL0911 is for lock timeouts
and deadlocks. The reason code would tell us which of the two cases
occurs, and the counter-measures will heavily depend on that.

> When fetchrow_arrayref
> fetches the locked row, I do get -911 which I trap in my code. When I
> move on to the next row doing another fetchrow_arrayref, I get error -3
> (no statement executing). Does that mean that fetchrow_arrayref
> will stop processing at the first error and there is no way to continue
> to the next stop, ignoring it.

Given that you are also posting to a Perl NG, I assume your application is
written in Perl?

Note that PHP has the rather stupid default behavior to query DB2 using FOR
UPDATE. Specifying FOR READ ONLY explicitly often helps a lot with locking
issues. Maybe something similar applies to Perl as well?

> DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
> which are locked by another session.

There are settings like DB2_SKIPDELETED, DB2_SKIPINSERTED, and
DB2_EVALUNCOMMITTED. Maybe changing those (read in the manual for their
exact implications) could help as well?

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Steven N. Hirsch - 24 Feb 2007 17:38 GMT

>> DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
>> which are locked by another session.
>
> There are settings like DB2_SKIPDELETED, DB2_SKIPINSERTED, and
> DB2_EVALUNCOMMITTED. Maybe changing those (read in the manual for their
> exact implications) could help as well?

I can't check the FM at the moment, but last I looked the DBD::DB2
driver supported a limited subset of connection and statement
attributes. For example, a few years ago I had to hack in code to
handle settings for distributed transactions (not sure if this ever made
it into the code base, although I submitted it to the maintainer).

Steve

Reply to this Message

Ian - 26 Feb 2007 07:19 GMT

>>> DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
>>> which are locked by another session.
[quoted text clipped - 8 lines]
> handle settings for distributed transactions (not sure if this ever made
> it into the code base, although I submitted it to the maintainer).

Those settings are instance-level configuration parameters, so you don't
have to worry about setting them at the application level.

Reply to this Message