Foreign keys and locking
Sometimes things are learnt the hard way. A couple of weeks ago, I was involved in purging some historical data from a production database using a set of DELETE statements. I assumed these transactions will not impact the Application (performance aside) because of how Oracle maintains read consistency and concurrency. This assumption was formed on weak knowledge on my part …
In short, the DELETE statements were executed on a parent table. It has foreign key on delete cascade relationships with several child tables. There were no indexes defined on the foreign key columns of the child tables. Whilst the DELETE statements were executing, the child tables became locked against other DMLs until a COMMIT or ROLLBACK is issued. Other DML transactions on these unindexed child tables appeared to hang, i.e. enqueued.
The on delete cascade constraint was the key, if it was disabled, locking would not happen but instead the transaction will fail with ORA-02292: constraint violation – child records found. However, enabling the on delete cascade constraint wass the right thing to do. To prevent locking, we just had to define indexes on the foreign key columns.
This apparent database design knowledge showed how little I know about Oracle, and how inexperienced I am.
I conjured a more simplistic example, and also found some reference to this in Metalink.
(Sorry… I am just being very silly with the example…)
Create tables:
Suppose I create two parent tables, and a child table with foreign keys linked to them. The foreign keys do not have indexes.
create table dad_table
(
id number(10),
name varchar2(10),
constraint pk_dad_table_id primary key (id)
);
create table mum_table
(
id number(10),
name varchar2(10),
constraint pk_mum_table_id primary key (id)
);
create table child_table
(
dad number(10)
constraint fk_child_dad_table_id
references dad_table(id)
on delete cascade,
mum number(10)
constraint fk_child_mum_table_id
references mum_table(id)
on delete cascade,
name varchar2(10)
);
SQL> select index_name from user_indexes
2 where table_name='CHILD_TABLE';
no rows selected
Populate data:
insert into dad_table (id, name) values (1, 'john'); insert into dad_table (id, name) values (3, 'peter'); insert into dad_table (id, name) values (5, 'david'); insert into mum_table (id, name) values (2, 'mary'); insert into mum_table (id, name) values (4, 'jane'); insert into mum_table (id, name) values (6, 'ivy'); insert into child_table (dad, mum, name) values (1, 2, 'ashton'); insert into child_table (dad, mum, name) values (3, 4, 'james');
Testing:
I am going to start three Oracle sessions, two as the schema user, one as SYSDBA which I will use to monitor locks. I used the locks information script from Metalink Note 1020012.6. I also found a script in Tom Kyte’s book (Expert Oracle Database Architecture – Chapter 6 – Locking and Latching) useful in displaying locks blocker and blockee.
i. Without indexes on foreign keys:
Session #1 (SID=11): SQL> delete from dad_table where id=1; 1 row deleted. Session #2 (SID=10): SQL> delete from mum_table where id=4; (... hangs) Session SYSDBA: * Note 1020012.6 - SID SERIAL# USERNAME Resource Type TAB Lock Held Lock Req. ---- ------- ---------- -------------------- ---------- -------------- -------------- 10 5596 FREDT TM - DML Enqueue DAD_TABLE Row Share 10 5596 FREDT TM - DML Enqueue MUM_TABLE Row Exclusive 10 5596 FREDT TM - DML Enqueue CHILD_TABL Shr Row Excl 11 3311 FREDT TM - DML Enqueue DAD_TABLE Row Exclusive 11 3311 FREDT TM - DML Enqueue MUM_TABLE Row Share 11 3311 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive * Tom's Blocker, Blockee and Object Name - BLOCKER SID BLOCKING BLOCKEE SID ON_OBJ OBJECT_NAME ---------- ------ --------------- ---------- ------ ------ -------------------- CIMS 11 is blocking CIMS 10 on CHILD_TABLE * V$Session_Wait - SQL> select sid, event from v$session_wait where event='enqueue'; SID EVENT ---- ---------------------------------------------------------------- 10 enqueue
Comments:
The two sessions were deleting different rows of data, but one was blocking the other. The lock information showed SID 10 was requesting a Share Row Exclusive lock (SSX) on the child_table, when a Row Exclusive (SX) lock on the same table was held by SID 11. This conflict is explained in 10g Concept Guide (the 9i Concept Guide html formatting looks a bit out of whack).
The puzzling thing is why request a SSX lock? Metalink Note 33453.1 explains this behaviour, but it still does not explain the *why*:
Without indexes… A share lock (LMODE=4) of the entire child table is required until the transaction containing the insert/delete/update statement for the parent table is committed, thus preventing any modifications to the child table. It even can be a SSX (LMODE=5) lock when deleting from the parent table with a delete cascade constraint. In 9.2.0, the downgraded ‘mode 3 Row-X (SX)’ locks are no longer required except when deleting from a parent table with a ‘delete cascade’ constraint.
Another interesting point – SID 11 also acquired a Row Share (SS) lock on mum_table. It isn’t immediately obvious, but a SS lock (only) prohibits other transactions from acquiring an Exclusive (X) lock on table, either explicitly (lock table … in exclusive mode) or implicitly (alter table). Note 223303.1 also made this statement:
Starting in version 9.2.0, a Row-SS lock is taken on the parent table for any DML issued against the child table. This will occur with or without an index on the foreign key column of the child table.
The table structure is somewhat similar to the application schema I was working on. Whilst I was deleting from the parent table, the Application was also deleting rows from another parent table, to which both parent tables are linked to a single child table. Given the above experiment, I now understand why the Application appeared to have “hang” and threads were reporting timeout errors.
ii. With indexes on foreign keys:
Let’s first create two indexes on the child table:
create index ix_dad_child on child_table(dad); create index ix_mum_child on child_table(mum); Session #1 (SID=11): SQL> delete from dad_table where id=1; 1 row deleted. Session #2 (SID=10): SQL> delete from mum_table where id=4; 1 row deleted. Session SYSDBA: * Note 1020012.6 - SID SERIAL# USERNAME Resource Type TAB Lock Held Lock Req. ---- ------- ---------- -------------------- ---------- -------------- -------------- 10 5596 FREDT TM - DML Enqueue DAD_TABLE Row Share 10 5596 FREDT TM - DML Enqueue MUM_TABLE Row Exclusive 10 5596 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive 11 3311 FREDT TM - DML Enqueue DAD_TABLE Row Exclusive 11 3311 FREDT TM - DML Enqueue MUM_TABLE Row Share 11 3311 FREDT TM - DML Enqueue CHILD_TABL Row Exclusive * Tom's Blocker, Blockee and Object Name - no rows selected * V$Session_Wait - SQL> select sid, event from v$session_wait where event='enqueue'; no rows selected
Comments:
The two sessions are no longer in blocker-blockee state, and each can delete their different rows. The indexes defined on the foreign key columns have resolved the locking scenario introduced in this example.
Note 33453.1 explains this:
An Insert/Delete/Update on the parent table will only acquire a row level lock on the parent table if there is an index on the foreign key of the child table. The child table will have NO locks on it and so any type of modifications can be made to the child table…
If the child table specifies ON DELETE CASCADE, waiting and locking rules are the same as if you deleted from the child table after performing the delete from the parent. In 9.2.0 onwards, Oracle requires ‘mode 2 Row-S (SS)’ locks on the child table.
Metalink References:
Note:33453.1 – REFERENTIAL INTEGRITY AND LOCKING.
Note:16428.1 – Prevent Deadlock in Existing Parent- Child Tables By INDEXING PK and FK.
Note:223303.1 – Correction to 9.2.0 foreign key constraint locking behaviour, per documentation.
Note:29787.1 – VIEW: “V$LOCK” Reference Note.
Note:11828.1 – FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING.
Frederick Tang Weblog 
Leave a comment