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.
14 comments

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.

Index Create and Rebuild Locking Improvements in 11g (Ch Ch Ch Changes) February 11, 2008

Posted by Richard Foote in 11g, Index Rebuild, Locking Issues, Oracle General, Oracle Indexes.
15 comments

Although the CREATE INDEX … ONLINE and ALTER INDEX … REBUILD ONLINE options have been available for a long while, they can still introduce locking issues in highly active databases.

Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

These locks have two implications. Firstly, if there’s an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required, the indexing process will hang. This will of course impact the time it takes to complete the indexing process. However the second far more serious issue is that any other active transactions on the base table starting after the indexing process hangs will likewise be locked and be prevented from continuing, until the indexing process obtains and releases its locks. In highly concurrent environments with many transactions, this can cause serious disruptions to the response times of these impacted transactions. Of course, depending on the time the initial locking transactions take to commit or rollback, this backlog of locked transactions can be quite significant.

Oracle11g has made some improvements in the locking implications regarding creating or rebuilding indexes online.

During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as its done previously until all these prior active transactions have completed. No change so far.

However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully. The indexing process no longer impacts other concurrent transactions on the base table, it will be the only process potentially left hanging while waiting to acquire its associated lock resource.

This means it may not be quite so “risky” to urgently introduce that new index or rebuild that troublesome index during core business hours due to the reduced locking implications introduced in 11g.

See this demo for Index Rebuild Locking Issues in 10g and the 11g Improvements.

Does this means we can now simply rebuild all our indexes, whenever ? Ummmm, no ;)

Follow

Get every new post delivered to your Inbox.

Join 1,818 other followers