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

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.
 

 
SQL> CREATE TABLE artists (id NUMBER PRIMARY KEY, artist_name VARCHAR2(30));
 
Table created.
 
SQL> CREATE TABLE formats (id NUMBER PRIMARY KEY, format_name varchar2(30));
 
Table created.
 
SQL> CREATE TABLE albums (id NUMBER, album_name VARCHAR2(30), artist_id NUMBER CONSTRAINT artist_fk REFERENCES artists(id), format_id number
 
CONSTRAINT format_fk REFERENCES formats(id));
 
Table created.
 
SQL> INSERT INTO artists VALUES (1, 'DAVID BOWIE');
 
1 row created.
 
SQL> INSERT INTO artists VALUES (2, 'PINK FLOYD');
 
1 row created.
 
SQL> INSERT INTO formats VALUES (1, 'CD');
 
1 row created.
 
SQL> INSERT INTO formats VALUES (2, 'DVD');
 
1 row created.
 
SQL> INSERT INTO albums VALUES (1, 'LOW', 1, 1);
 
1 row created.
 
SQL> INSERT INTO albums VALUES (2, 'DIAMOND DOGS', 1, 1);
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.

    

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):

  
SQL> CREATE INDEX albums_format_i on albums(format_id);
 
Index created.
 
SQL> insert into artists values (3, 'MUSE');
 
1 row created.

 

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.