Referential Integrity and Locks

When it comes to enforcing parent-child constraint, DB2 is not as aggressive compared to Oracle. This can be demonstrated easily:

DB2:

Create a table P1 ( with a single column C1), and another table C1 which references P1 as foreign key owner. Populate Table P1 so that value '1' is in there.
(ok, I admit the choice of table name C1 is confusing at best, given that there is a column name called 'c1' in P1 ... )

Remember to commit.

Now, start up another CLP and issue this:

LOCK TABLE C1 IN EXCLUSIVE MODE

At this point, this CLP session would have exclusive lock on table C1.


Now, start up another CLP session, and issue this:

UPDATE P1 SET C1 = 2 WHERE C1 = 1

You will have no problem with that. The update will succeed. There is only one IX lock on table P1 which is owned by this session.


For Oracle, the situation is different. When you issue UPDATE P1 SET C1 = 2 WHERE C1 = 1, this session will attempt to obtain an SX lock (mode 3) on table C1, and it will block because of the exclusive lock by another session ( with the 'LOCK TABLE xxxx' command).

It does raise the question: which one is 'safer'? What if a single call is needed to get a consistent view of , say, join between P1 and C1? The answer will be revealed in future post ...