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). 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:
What Oracle version was used for this exercise?
Is it possible that locks and their modes are being changed between versions (not only major)?
This is quite an interesting observation. Thanks for sharing it.
[…] Keys and Library Cache […]
[…] Keys and Library Cache […]
Very useful article
the validation and the enabling are two different steps
but if done in the same command then either both success or both failed
the enabling changes the library cache object because new dmls need to know it is there for parsing.
so the libcache lock will be held until the complete execution of both the enabling and the validation as it is considered one atomic operation as it is one command and grabs the enqueues because of the validation and holds until finish.
if done in two steps then lib cache lock is release when the statement finishes because no validation but the constraint will be enforced for future dmls.
then when the validate is done later,the validate will hold the enqueues but no lib cache lock because the validate does no need to change the lib objects. it is a status on the dd.
Hi Abel
Thank you for commenting.
I’m fine with the fact that it is a two step operation and that the whole operation must succeed or fail and, therefore, addition locks are necessary. What I do not understand is why the locks on the PARENT table are necessary. And, honestly, your comments do not provide any information about that point.
My point is quite simple… Modifying the structure of a CHILD2 table has no impact whatsoever with the PARENT table. The only “relation” is that when for some DML statements on the PARENT table the CHILD table might need to be locked. But that is something that should be covered by locks on the CHILD table, not on the PARENT table. So, I would love to know in which case such a behavior is necessary/useful.
Best,
Chris
[…] l’explique d’ailleurs ici Christian Antognini (foreign-keys-and-library-cache-locks), la bonne pratique pour diminuer le temps de verrouillage d’une table pendant ces […]