Queston on DB2 lock

[xeqister]

Greetings,
We are connecting to a DB2 database residing on an AIX machine via
JDBC.

In order to lock some row of records, we use the "select . . . for
update . . . " Java statement:

In a multi user environment, we find that once a user locks the rows of
records, another user executing the "select ... for update . . . "
statement will wait, until the first user "commits" the transaction, so
that the rows will be unlocked.

Please let us know if there is a 'Non blocking" locking mechanism,
whereby, if the rows are locked, the statement fails with an
appropriate error message that could be traped, so that the application
could behave accordingly.

We understand that some database supports a "nowait" option, in the
"select" statement, i.e. "select for update, nowait . . . ". Is there
something equivalent in DB2?

Thanks in advance..

[Mark A]

<xe******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Greetings,
We are connecting to a DB2 database residing on an AIX machine via
JDBC.

In order to lock some row of records, we use the "select . . . for
update . . . " Java statement:

In a multi user environment, we find that once a user locks the rows of
records, another user executing the "select ... for update . . . "
statement will wait, until the first user "commits" the transaction, so
that the rows will be unlocked.

Please let us know if there is a 'Non blocking" locking mechanism,
whereby, if the rows are locked, the statement fails with an
appropriate error message that could be traped, so that the application
could behave accordingly.

We understand that some database supports a "nowait" option, in the
"select" statement, i.e. "select for update, nowait . . . ". Is there
something equivalent in DB2?

Thanks in advance..

The default setting for LOCKTIMEOUT in the db config is -1, which means that
DB2 will wait forever (unless a deadlock occurs). You can change that to the
number of seconds you want to wait before a -911 occurs. However, an
automatic rollback may occur with the transaction that gets the -911, so
test it to make sure it behaves as desired.

[Shyam Peri]

The following link could possibly help you out with what you are trying
to achieve

http://publib.boulder.ibm.com/infoce...n/r0011874.htm

[Shyam Peri]

The following link could possibly help you out with what you are trying
to achieve

http://publib.boulder.ibm.com/infoce...n/r0011874.htm

[Walter Schneider]

Hello,

a possible way to prevent your appl from either waiting for a lock or
failing with SQLCODE -911 (which includes an undesired rollback), you could
try to declare a static sensitive scrollable cursor to process your desired
rowset. Scrollable cursors use a kind of optimistic locking mechanism which
does not lock the original rows anymore after OPEN. If you update a row
within the open scrollable cursor then, but another process has already
updated this row in the meantime, your appl still gets an error sqlcode
(-224 afaik), but it will NOT be rolled back and thus can still decide what
do do now instead...

Regards - Walter.
<xe******@gmail.com> schrieb im Newsbeitrag
news:11**********************@g14g2000cwa.googlegr oups.com...

Greetings,
We are connecting to a DB2 database residing on an AIX machine via
JDBC.

In order to lock some row of records, we use the "select . . . for
update . . . " Java statement:

In a multi user environment, we find that once a user locks the rows of
records, another user executing the "select ... for update . . . "
statement will wait, until the first user "commits" the transaction, so
that the rows will be unlocked.

Please let us know if there is a 'Non blocking" locking mechanism,
whereby, if the rows are locked, the statement fails with an
appropriate error message that could be traped, so that the application
could behave accordingly.

We understand that some database supports a "nowait" option, in the
"select" statement, i.e. "select for update, nowait . . . ". Is there
something equivalent in DB2?

Thanks in advance..