Consider I have three tables: table_1, table_2 and table_3. They are as follows:

==============
TABLE_1
==============
table_1_id (PK)
data_col
==============
==============
TABLE_2
==============
table_2_id (PK)
table_1_id (FK to table_1)
==============
==============
TABLE_3
==============
table_3_id (PK and FK to table_1)
table_2_id (FK to table_2)
==============

Consider that we have the following contents in the above tables (just one row each):

- Table 1: table_1_id=1 and data_col='value'
- Table 2: table_2_id=1 and table_1_id=1
- Table 3: table_3_id=1 and table_2_id=1

The problem happens when I execute the following statements in the following order:

1. Session 1: update table_1 set data_col='deadlock' where
table_1_id=1;

2. Session 2: update table_2 set table_1_id=1 where table_2_id=1; --This update is locked until the update in session 1 is committed.

3. Session 1: update table_3 set table_2_id=1 where table_3_id=1; --This update is locked until the update in session 2 is committed.

This way we have a deadlock (session 1 is waiting on session 2 which is
waiting on session 1).

One of the possible solutions is to make DB2 not lock rows on the
related tables for foreign key updates, considering only commited data
to validate foreign keys. This is what makes more sense to me. Maybe
there are other alternatives to solve this kind of deadlock, but by now
I don't know them.

Note that there is no point (or at least I can't see any point) in these updates waiting on a lock of the above update because the primary keys related to these foreign keys are not being updated. Okay, I know that it is a row lock and (of course) it locks the entire row. There is no such thing like "column lock", but I think that it is the most desirable kind of lock in this specific case.
Then the lock wait would occur only when needed.

Even in uncommitted read isolation level this problem happens (using "update ... with ur"), but anyway using UR is not a choice.

I've been searching forums, documentation, newsgroups and the IBM pdfs for a couple of days but have found nothing (at least I have learned a lot about how DB2 uses locks, lock waits, isolation levels, etc.).

Any help here will be very appreciated.

Thanks!
Felipe de Alvarenga Leite

UR does not apply to updates, inserts, or deletes.

Try setting this parameter

db2 set DB2_EVALUNCOMMITTED=ON

Also consider using the following:

DB2_SKIPINSERTED=ON
DB2_SKIPDELETED=ON

If the tables are so small that DB2 does not use an index to retrieve the
rows, then alter the relevant tables to "volatile".

Hi Marcus_A

in point of view data integrity is it possible these registery variables make problem?

There are some specific situations where these registery variables should not be used (when set the they apply to the entire DB2 instance).

On example is when there is control table that contains a value such as next available sequence number or other value that is always expected to be present in the application for all users connected. If an application that changes the control value does a delete and then an insert instead of an update in a single unit of work, this could cause problems if the DB2_SKIPINSERTED=ON and DB2_SKIPDELETED=ON registery values are present, and the control value is always expected to be available to other applications.

Even if the control value has been udpated, then other applicaitons that did a read only on the value, the other application SQL might evaluate it before it is commited with the DB2_EVALUNCOMMITTED=ON.

I am sure there are some other unusal application situations where there could be logic problems, but for most applications it should not be a problem.

DB2_SKIPINSERTED=ON is the safest to use for all applications.