How to do a safe “SELECT FOR UPDATE” with a WHERE

Question

Problem

On a DB2 (version 9.5) the SQL statement

SELECT o.Id FROM Table1 o, Table2 x WHERE [...] FOR UPDATE WITH RR

gives me the error message SQLSTATE=42829 (The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified).

Additional info

I need to specify WITH RR, because I'm running on isolation level READ_COMMITTED, but I need my query to block while there is another process running the same query.

Solution so far...

If I instead query like this:

SELECT t.Id FROM Table t WHERE t.Id IN (
    SELECT o.Id FROM Table1 o, Table2 x WHERE [...]
) FOR UPDATE WITH RR

everything works fine.

New problem

But now I occasionally get deadlock exceptions when multiple processes perform this query simultaneously.

Question

Is there a way to formulate the FOR UPDATE query without introducing a place where a deadlock can occur?

Answer 1

First, for having isolation level READ_COMMITTED you do not need to specify WITH RR, because this results in the isolation level SERIALIZABLE. To specify WITH RS (Read Stability) is enough.

To propagate the FOR UPDATE WITH RS to the inner select you have to specify additionally USE AND KEEP UPDATE LOCKS.

So the complete statement looks like this:

SELECT t.Id FROM Table t WHERE t.Id IN (
    SELECT o.Id FROM Table1 o, Table2 x WHERE [...]
) FOR UPDATE WITH RS USE AND KEEP UPDATE LOCKS

I made some tests on a DB2 via JDBC and it worked without deadlocks.