Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door) November 10, 2010

As I’ve been focusing mainly with Oracle 11g at work these days, thought I might look at a number of Oracle 11g related topics in the coming weeks.

To start with, there’s been a subtle but potentially significant change introduced in Oracle 11g (since 11.1.0.6) with regard to the manner in which locks are held in relation to policing Foreign Key constraints. The following has been tested on both 11.2.0.1 and 11.2.0.2.

To set the scene and replicate the issue we hit at work, I’m just going to create a little table (ALBUMS) that has 2 FK constraints pointing to two parent tables (ARTISTS and FORMATS) and populate them with a few rows.

OK, when running the following insert statement on the ARTISTS table in 10.2.0.3:

SQL> insert into artists values (3, 'MUSE');
1 row created.

A check in the v$lock view will show the transaction holds a TM (DML Enqueue) lock in row-S (SS) mode 2 on the child ALBUMS table due to the FK relationship between these tables.

If another session were to either say delete a row or update the PK from the other parent FORMATS table:

SQL> update formats set id = 2 where id = 2;
1 row updated.

It will succeed with no problem for when it temporarily requires a TM share (S) mode 4 lock on the ALBUMS table, it can successfully grab it as the concurrent SS lock does not prevent this from occurring. It requires access to this mode 4 Share lock to ensure there are no transactions currently impacting the ALBUMS table that could potentially violate the constraint following the DML operations on the parent FORMATS table.

However, repeating the same exercise in Oracle 11g and we hit a subtle difference. When running the insert statement again in the ARTISTS table:

SQL> insert into artists values (3, 'MUSE');
1 row created.

A check in the v$lock view will now show the transaction holds a TM (DML Enqueue) lock in row-X (SX) LMODE 3 on the child ALBUMS table, not a LMODE 2 SS level lock as it did in 10g. This is a “higher” level lock mode which has the following consequence on the other session now attempting to either delete or update the PK in the FORMATS table:

SQL> update formats set id = 2 where id = 2;

The session now hangs as it has to wait for the other session to release the DML Enqueue LMODE 3 SX lock before it can in turn grab the required TM mode 4 Share table lock it’s requesting. This is precisely the issue we hit with a somewhat poorly written application trying to perform something akin to the above series of updates from within two different sessions.

This change was introduced by Oracle to eliminate an ORA-600 issue that could occur when deleting a row from a table with a PK while rebuilding an associated FK index that referenced the PK.

However, introducing a more restrictive level of lock in this manner has the side-effect of increasing the likelihood of encountering new locking issues such as this, increasing the likelihood of hitting deadlock scenarios (as discussed here previously by Charles Hooper) and can therefore potentially reduce the overall concurrency capabilities of an application.

The “fix” in this case is to simply create an index on the formats_id FK column (which probably should exist anyways in this case to prevent locking issues on the child table when updating the parent FORMAT table):

In which case the table share lock is no longer required on the ALBUMS table (as Oracle can now use the associated index to effectively police the integrity of the child table following such an operation on a parent table) and the statement no longer hangs in the other session:

SQL> update formats set id = 2 where id = 2;
1 row updated.

This change in the locking behaviour of policing FK constraints is certainly something to be aware of when migrating to Oracle 11g if you potentially have FK constraints that don’t have associated indexes.

“…with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:
– Update the parent table primary key
– Delete from the parent table
– Merge into the parent table

And the rule in Oracle Database 11g Release 1 and later is that you will want an index on the foreign key of the child table if you do any of the following:
– Update the parent table primary key
– Delete from the parent table
– Use a merge that either updates the parent table primary key or deletes from the parent table
“

This is exactly what we’ve been seeing at a client site doing an upgrade from 9i to 11gR2. Only a handful of FKs in the 9i database were indexed and it was hard to be convincing as to why it would help in the 11gR2 database. This explanation is exactly what I needed. Thanks again, Richard!

The problem with foreign key constraints lacking a supporting index in Oracle is not a new one. Updating a primary key (you shouldn’t do that anyway) or deleting a row in the parent table needs to make sure that no entry referencing the old row is cre…

[…] I came across this article. I also read this article “Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)” by Richard Foote. These articles states that there has been a change since 11.1.0.6 on how locks […]

[…] together a related article that explained the changes in locking mode with foreign keys in his Oracle11g: New Locking Modes When Policing FK Constraints article. Trying not to forget related articles (see the note at the end of this blog article), […]

As always, the merits of an index needs to be balanced with the overheads associated with having to store and maintain the index.

So what are the actual performance implications on creating such indexes ? Are insert rates and other DML performance metrics acceptable ?

Why are you creating indexes on the FKs ? Are you performing deletes on the parents tables ? Do you need the indexes in place for SQL performance based on predicates on the FK columns ? If not, then the indexes may be unnecessary.