In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way.

Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). Schema used for the tests In my case every table is owned by a different schema (P, C1 and C2 respectively). But, the behavior I describe is independent from that fact (i.e. it works in the same way if all tables are owned by the same schema). If you are interested, here is the SQL*Plus script I used to create them.

What I want to describe is what happens if there is a session that inserts data into CHILD2 and, concurrently, another session enables a disabled foreign key (CHILD1_PARENT_FK) on CHILD1. But, before looking at what happens when such operations are concurrently executed, let’s review which locks are taken when the two SQL statements are not concurrently executed. Note that to have a list of the locks I use event 10704 at level 16 (I don’t use a V$ or data dictionary view because I want to have a list of all locks involved, not just the locks that are held at a specific time). And, since event 10704 references the objects through their ID in hexadecimal form, here is the mapping between the table names and their ID.

SQL> SELECT object_id, to_char(object_id,'0XXXXXXX') AS object_id_hex, object_name
  2  FROM dba_objects
  3  WHERE (owner = 'P' AND object_name = 'PARENT')
  4  OR (owner = 'C1' AND object_name = 'CHILD1')
  5  OR (owner = 'C2' AND object_name = 'CHILD2');
 
 OBJECT_ID OBJECT_ID_HEX OBJECT_NAME
---------- ------------- --------------------
    106390  00019F96     PARENT
    106392  00019F98     CHILD1
    106399  00019F9F     CHILD2

To check what the enable foreign key constraint statement does, I use the following SQL statements:

SQL> CONNECT c1/c1
SQL> ALTER TABLE child1 DISABLE CONSTRAINT child1_parent_fk;
SQL> ALTER SESSION SET events = '10704 trace name context forever, level 16';
SQL> ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;

The trace file lines related to the three tables of the test schema are the following (note that the function ksqgtl gets the lock and the function ksqrcl releases it):

*** 2015-08-27 20:52:28.623
ksqgtl *** OD-00019F98-00000000-00000000-00000000 mode=4 flags=0x10400 timeout=0 ***
*** 2015-08-27 20:52:28.624
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-27 20:52:28.625
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-27 20:52:51.107
ksqrcl: TM-00019F96-00000000-00000000-00000000
*** 2015-08-27 20:52:51.107
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-27 20:52:51.107
ksqrcl: OD-00019F98-00000000-00000000-00000000

Based on this information, three locks are taken:

  • An OD lock in shared mode (4) to prevent concurrent online DDL statements
  • A shared table lock (TM in mode 4) on PARENT
  • A shared table lock (TM in mode 4) on CHILD1

Also note that the locks are released about 22 seconds after being got. This is because the validation of the constraint took about 22 seconds and, during the validation, the locks must be hold.

To check what the INSERT statement does, I use the following SQL statements:

SQL> CONNECT c2/c2
SQL> ALTER SESSION SET events = '10704 trace name context forever, level 16';
SQL> INSERT INTO child2 SELECT 2, 1, NULL FROM child2;

The trace file lines related to the three tables of the test schema are the following (note that since the transaction is neither committed nor rolled back, the locks are not released):

*** 2015-08-27 20:47:54.437
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***
*** 2015-08-27 20:47:54.437
ksqgtl *** TM-00019F9F-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***
*** 2015-08-27 20:47:54.438
ksqgtl *** TX-0004000C-00001D1C-00000000-00000000 mode=6 flags=0x401 timeout=0 ***

Based on this information, three locks are taken:

  • A subexclusive table lock (TM in mode 3) on PARENT
  • A subexclusive table lock (TM in mode 3) on CHILD2
  • An exclusive transaction lock (TX in mode 6)

Now that I have described what locks are involved, it’s time to describe what happens when the two operations are concurrently executed. It goes without saying that even though the two operations are executed concurrently, one of the two is actually executed before the other. Hence, let’s discuss both cases:

  • INSERT statement first: the SQL statement that enables the constraint can’t be executed until the transaction that inserts the row terminates (note that the one that disables the constraint doesn’t have such a limitation). By default, the ALTER TABLE statement raises an ORA-00054. To avoid the error, it’s possible to specify a timeout through the DDL_LOCK_TIMEOUT parameter. The following video shows an example.

  • ALTER TABLE statement first: the SQL statement that inserts the row can’t be executed until the SQL statement that enables the constraint terminates. The strange thing, at least for me, is that the INSERT statement waits on a “library cache lock”. The following video shows an example.

As the previous video shows, even though the ALTER TABLE statement executed on CHILD1 can’t impact the outcome of the INSERT statement executed against CHILD2, the INSERT statement waits on a “library cache lock” until the execution of the ALTER TABLE statement terminates.
Completely avoiding such waits isn’t possible. That said there is a workaround to mitigate their impact: enable the constraint in two steps as shown by the following SQL statements:

ALTER TABLE child1 ENABLE NOVALIDATE CONSTRAINT child1_parent_fk;
ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;

What’s the difference from a locking point-of-view?

With the help of event 10704, let’s have a look to the locks that are got during the execution of these two SQL statements.

  • The ENABLE NOVALIDATE statement, as the following information shows, gets two shared table locks (TM in mode 4) on PARENT and CHILD1 (notice that since no validation takes place, they are immediate released):
*** 2015-08-28 03:00:48.872
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-28 03:00:48.872
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-28 03:00:48.886
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:00:48.886
ksqrcl: TM-00019F96-00000000-00000000-00000000
  • The ENABLE statement, as the following information shows, uses a three steps approach. First, it gets an OD lock lock in shared mode (4) and a subshare table lock (TM in mode 2) on CHILD1. But, notice the timestamps, it immediately releases them. Second, it validates the constraint (in this case it takes about 27 seconds). Third, it gets and immediately releases a subshare table lock (TM in mode 2) on CHILD1.
*** 2015-08-28 03:06:40.680
ksqgtl *** OD-00019F98-00000000-00000000-00000000 mode=4 flags=0x10400 timeout=0 ***
*** 2015-08-28 03:06:40.681
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=2 flags=0x400 timeout=0 ***
*** 2015-08-28 03:06:40.696
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:06:40.696
ksqrcl: OD-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:07:07.803
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=2 flags=0x400 timeout=0 ***
*** 2015-08-28 03:07:07.821
ksqrcl: TM-00019F98-00000000-00000000-00000000
ksqrcl: returns 0

As a result, with the two-steps approach (an ENABLE NOVALIDATE followed by an ENABLE), the contention issues due to foreign key can be mitigated. The following video shows an example: