jump to navigation

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

Posted by Richard Foote in 11g, Foreign Keys, Locking Issues, Oracle Indexes.
trackback

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.

About these ads

Comments»

1. dombrooks - November 15, 2010

Thanks for the succint demonstration.

I’m reminded of Nick Rivers in Top Secret – “Things change, people change, hairstyles change …”

Foreign Key locking seems to undergo a subtle change on this in every major release, hence the benefit of such test cases, etc.

Eventually the old “rule of thumb” of “always index your foreign keys” will become a complete myth.

Jonathan Lewis also has a related article:

http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/

In his latest oramag column,
http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60asktom-176254.html
Tom Kyte has this guidance:

“…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

Richard Foote - November 24, 2010

Hi Dom

Thanks for the links and yes, I agree with all that you’ve said :)

2. Michael Paddock - November 20, 2010

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!

Richard Foote - November 24, 2010

Hi Michael

Cool, glad it helped to explains things a bit.

3. BlogNotes to Myself - December 8, 2010

Oracle and Foreign Keys without Index…

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…

4. al0 - February 14, 2011

Any guesses why this change was introduced?

Richard Foote - February 18, 2011

Hi Alo

As I mentioned in the blog piece:

“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.”

It addresses bug 5909305.

al0 - February 18, 2011

Thanks, I already found that bug number. Dare say a classical case of “the remedy is worse than the disease”.

5. Matt McClernon - February 15, 2011

Your ‘fix’ didn’t work in my case on 11.1.0.7 (even after adding an index I still get the Mode 3 lock on the child):

Session 1 (NORMAL USER):

create table parent (id number primary key, parent_descr varchar2(10));

create table child (id number primary key, parent_id number, child_descr varchar2(10));

alter table child add constraint child_fk foreign key (parent_id) references parent(id);

insert into parent values (1,’Parent One’);
insert into child values (1,1,’Child One’);
commit;

insert into parent values (2,’Parent Two’);

Session 2 (SYSDBA):

select sid from v$session where program like ‘sqlplus%’ and username = ‘ORER';

col object_name form a20
select l.type, do.object_name, l.id2, l.lmode, l.request
from v$lock l
left outer join dba_objects do on do.object_id = l.id1
where sid = 524

TY OBJECT_NAME ID2 LMODE REQUEST
— ————————- ———- ———- ———-
TM CHILD 0 3 0
TM PARENT 0 3 0
AE ORA$BASE 0 4 0
TX 37739 6 0

Session 1 (NORMAL USER):

rollback;

create index childidx on child(parent_id);

insert into parent values (2,’Parent Two’);

Session 2 (SYSDBA):

select sid from v$session where program like ‘sqlplus%’ and username = ‘ORER';

col object_name form a20
select l.type, do.object_name, l.id2, l.lmode, l.request
from v$lock l
left outer join dba_objects do on do.object_id = l.id1
where sid = 524

TY OBJECT_NAME ID2 LMODE REQUEST
— ————————- ———- ———- ———-
TM CHILD 0 3 0
TM PARENT 0 3 0
AE ORA$BASE 0 4 0
TX 63935 6 0

Is this a bug..?

Richard Foote - February 18, 2011

Hi Matt

Likely not.

I don’t have a copy of 11.1.0.7 at hand right now, however it’s not such much the locking level but whether a session is in turn impacted by such a lock.

The key test is whether the session is actually locked in your example or not. Can a transaction in session 2 proceed or not with the index in place ?

6. jcon.no: Oracle Blog - After upgrading from Oracle 9i to 11g - February 10, 2012

[...] 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 [...]

7. Sasi - April 18, 2013

Index creation didn’t fix my problem, Oracle version is 11.2.3.

I have created index for all FK constraints in the database, but still my update query in session 2 gets stuck. Can you please let me know, is there any thing more to check?

Is there any way to find the constraint which block the child table record?

Richard Foote - April 30, 2013

Hi Sasi

As I mentioned in the blog piece, have a look at v$lock and see what locks are still in place and take it from there.

8. Oracle Enqueue Rules Leading to Deadlock Changes Again in 12.1? | Charles Hooper's Oracle Notes - August 2, 2013

[…] 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), […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,861 other followers

%d bloggers like this: