SELECT ... FOR UPDATE not locking rows

[jbutler8192]

Hello.

We have a multi-user Java application that can connect to either a
UniVerse database or a DB2 database on an AS400 through JDBC. The
program executes a SELECT ... FOR UPDATE statement. When connected to
the UniVerse database, if a second user runs the program, at the point
where the selection executes, the program waits on the lock until the
first user's updates are committed. However, when connected to the DB2
database, in the same situation, execution proceeds as if the FOR
UPDATE were not there.

Any ideas on what I should be looking for? Thanks.

[Karl Hanson]

jb*********@sbcglobal.net wrote:

Hello.

We have a multi-user Java application that can connect to either a
UniVerse database or a DB2 database on an AS400 through JDBC. The
program executes a SELECT ... FOR UPDATE statement. When connected to
the UniVerse database, if a second user runs the program, at the point
where the selection executes, the program waits on the lock until the
first user's updates are committed. However, when connected to the DB2
database, in the same situation, execution proceeds as if the FOR
UPDATE were not there.

Any ideas on what I should be looking for? Thanks.

See the definition of update-clause here:
http://publib.boulder.ibm.com/infoce...zmstintsel.htm

Excerpts:
"The UPDATE clause identifies the columns that can be updated in a
subsequent positioned UPDATE statement."
"When FOR UPDATE is used, FETCH operations referencing the cursor
acquire an exclusive row lock."

--
Karl Hanson

[jbutler8192]

On Mar 21, 3:17 pm, Karl Hanson <[e-mail]:

[e-mail] wrote:

Hello.

We have a multi-user Java application that can connect to either a
UniVerse database or a DB2 database on an AS400 through JDBC. The
program executes a SELECT ... FOR UPDATE statement. When connected to
the UniVerse database, if a second user runs the program, at the point
where the selection executes, the program waits on the lock until the
first user's updates are committed. However, when connected to the DB2
database, in the same situation, execution proceeds as if the FOR
UPDATE were not there.

Any ideas on what I should be looking for? Thanks.

See the definition of update-clause here:http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...

Excerpts:
"The UPDATE clause identifies the columns that can be updated in a
subsequent positioned UPDATE statement."
"When FOR UPDATE is used, FETCH operations referencing the cursor
acquire an exclusive row lock."

--
Karl Hanson

Thanks. I'm not sure this is the exact solution you were pointing me
toward, but adding an isolation clause WITH RR to the select when
connected to DB2 did the trick.

[Weirdwoolly]

On Mar 21, 9:08 pm, [e-mail] wrote:

On Mar 21, 3:17 pm, Karl Hanson <[e-mail]:

[e-mail] wrote:

Hello.

We have a multi-user Java application that can connect to either a
UniVerse database or a DB2 database on an AS400 through JDBC. The
program executes a SELECT ... FOR UPDATE statement. When connected to
the UniVerse database, if a second user runs the program, at the point
where the selection executes, the program waits on the lock until the
first user's updates are committed. However, when connected to the DB2
database, in the same situation, execution proceeds as if the FOR
UPDATE were not there.

Any ideas on what I should be looking for? Thanks.

See the definition of update-clause here:http://publib.boulder.ibm.com/infoce...ndex.jsp?topic...

Excerpts:
"The UPDATE clause identifies the columns that can be updated in a
subsequent positioned UPDATE statement."
"When FOR UPDATE is used, FETCH operations referencing the cursor
acquire an exclusive row lock."

--
Karl Hanson

Thanks. I'm not sure this is the exact solution you were pointing me
toward, but adding an isolation clause WITH RR to the select when
connected to DB2 did the trick.- Hide quoted text -

- Show quoted text -

Hi,

Did this really do the trick?

The reason I ask is that we have also experienced deadlocks between
two instances of the same code that uses a cursor to ensure the record
is locked prior to any processing.

The row is identified using the PK of the table, so only one row is
ever identified and hopefully locked and tracing confirms that both
processes were using the same primary key. My understanding of the FOR
UPDATE clause and FETCH, was that one process would wait for the
other, however DB2 does not appear to behaving this way (at least not
in v8.2.5 anyway).

You may have solved our problem if it is true.

Regards,
Paul.