By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

Queston on DB2 lock

P: n/a
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..

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/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.
Nov 12 '05 #2

P: n/a
The following link could possibly help you out with what you are trying
to achieve

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

Nov 12 '05 #3

P: n/a
The following link could possibly help you out with what you are trying
to achieve

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

Nov 12 '05 #4

P: n/a
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..

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.