Lock Horror

Health Warning: It’s possible to insert rows into a table which is locked in exclusive mode by another session – and vice versa, it’s possible to lock in exclusive mode a table that has uncommitted inserts from another session.

Jump to the most recent status reports

Here’s an extract from a  simple script I’ve run from time to time over the last 10 years to create a little testbed to check up on “foreign key locking”. I haven’t run it for some time (my notes say the last version I ran it on until very recently was 10.1.0.2). All it does is create a pair of tables with a foreign key constraint between them and an index protecting that constraint from the foreign key locking problem:

em
rem     Script:         c_pk_fk_2.sql
rem     Dated:          Mar 2003
rem     Author:         Jonathan Lewis
rem

create table parent (
        id      number(4),
        name    varchar2(10),
        constraint par_pk primary key (id)
)
;

create table child(
        id_p    number(4)
                constraint chi_fk_par
                references parent,
        id      number(4),
        name    varchar2(10),
        constraint chi_pk primary key (id_p, id)
)
;

insert into parent values (1,'Smith');
insert into parent values (2,'Jones');

insert into child values(1,1,'Simon');
insert into child values(1,2,'Sally');

insert into child values(2,1,'Jack');
insert into child values(2,2,'Jill');

commit;

Following up a blog note from Charles Hooper on deadlocks in 11g, and then more recently a question on the Oracle database forum, I was moved to do some testing on 10.2.0.1, 10.2.0.3, and 11.1.0.6 – the preliminary results were worrying. Initially all I wanted to do was check what locks appear when you insert or delete child or parent rows, and then repeat the tests with table locking disabled on the child table.

Here are some results from 10.2.0.3

rem     Insert into parent - child is locked in mode 2, parent in mode 3
rem     Delete from parent - child is locked in mode 2, parent in mode 3
rem     Delete from child - parent is locked in mode 2, child in mode 3
rem     Insert into child - parent is locked in mode 2, child in mode 3

This was as I expected – the mode 2 (row share) lock at the “opposite end” of the foreign key constraint is something that appeared in 9.2.0.1. As you will see if you read my comment on Charles’ blog item this mode 2 changes to a mode 3 (row exclusive) in 11g – a change described in Metalink bug note 5909305 and described as a “Notable Fix” in Metalink note 454506.1.

For each test I was executing a statement such as ‘insert into parent …’, then running a query to check for the locks I held, then rolling back. The query I ran to check for locks was as follows:

select
        type, id1, id2, lmode, request
from
        v$lock
where
        sid = (select sid from v$mystat where rownum = 1)
;

After the first pass of tests listed above I started to repeat the sequence of tests after issuing “alter table child disable table lock;”. The effect of this change makes it impossible to lock the table in modes 4, 5, or 6 (though you can still lock the table in modes 2 or 3 – respectively the “select for update” mode (until 9.2.0.5) and the “insert/update/delete” mode.

Check this specific sequence of events, starting from a fresh build of the tables:

SQL> alter table child disable table lock;

Table altered.

SQL> insert into parent values(3,'asdf');

1 row created.

SQL> select
  2     type, id1, id2, lmode, request
  3  from
  4     v$lock
  5  where
  6     sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TX     655405       2025          6          0

1 row selected.

Look at that output carefully: I’ve just inserted a row into the parent table after disabling locks on the child table. My session is reporting NO table locks – despite an uncommitted row in the parent table !

Going to another session, look at this cut-n-paste (which demonstrates that I should be able to see a lock on the parent, even though I don’t expect to see a lock on the child (you’ll have to trust me that 49638 is the object_id of the parent at this point):

SQL> lock table parent in row exclusive mode;

Table(s) Locked.

SQL> lock table child in row exclusive mode;

Table(s) Locked.

SQL> select
  2     type, id1, id2, lmode, request
  3  from
  4     v$lock
  5  where
  6     sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TM      49638          0          3          0

1 row selected.

And here’s the “gasp, shock, horror” effect:

SQL> lock table parent in exclusive mode;

Table(s) Locked.

SQL> select
  2      type, id1, id2, lmode, request
  3   from
  4      v$lock
  5   where
  6      sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TM      49638          0          6          0

1 row selected.

This session is now holding an exclusive lock on the parent table when another session has an uncommitted row inserted into it.

I haven’t checked if there’s a bug report for this on Metalink (MOS), nor what happens in 10.2.0.4, 11.1.0.7 or 11.2.0.1. I haven’t played around with changing the child table to an index organized table (IOT), or even checked whether (unlikely though it may seem) this behaviour is just an odd side effect of the slightly confusing column names I’ve used. And I haven’t considered creating multiple child tables to check for stranger side effects (which is what the OTN posting was about).

If your code  uses “disable table lock” anywhere, though, you might want to do some very careful checks to make sure it’s still behaving for you.

Update – the following morning:

Here are some results from 11.1.0.6 after creating the tables and indexes as above, then restarting the session – a single cut-n-paste of an SQL*Plus sessions. (I’ve saved the query against v$lock into afiedt.buf to avoid cluttering the page.) Comments appear after each output:

SQL> insert into parent values (3,'asd');

1 row created.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0
TM      86862          0          3          0
TM      86864          0          3          0
TX     327690      18287          6          0

4 rows selected.

When we insert into the parent, the child gets locked in mode 3 (row exclusive), not mode 2 (row share). (The AE lock is the 11g “Application Edition” lock).

SQL> commit;

Commit complete.

SQL> delete from parent where id = 3;

1 row deleted.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0
TM      86862          0          3          0
TM      86864          0          3          0
TX     262148      13832          6          0

4 rows selected.

When we delete a row with no children – the one we’ve just inserted – we take a mode 3 lock on the child.

SQL> rollback;

Rollback complete.

SQL> alter table child disable table lock;

Table altered.

SQL> delete from parent where id = 3;
delete from parent where id = 3
            *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for CHILD

Since we rolled back the delete, the parent row 3 will still be available for deletion. If we try to delete it with table locking disabled on the child we fail because we can’t lock the child.

SQL> lock table child in row exclusive mode;

Table(s) Locked.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0

1 row selected.

SQL>

On the other hand, we can lock the child table in mode 3 (row exclusive) – even though the lock doesn’t show up in v$lock. (And we can insert into the parent without running into a locking problem – apart from the fact that the expected mode 3 lock doesn’t exist in v$lock and the table can be locked exclusively from another session).

Footnote:

In the past I’ve often advised people that if they want to have a bit of protection when they think it might be safe to drop a particular foreign key index they could always disable table locking on the child table (thus restricting the ensuing problems to the session that wasn’t supposed to be locking the table anyway). That piece of advice is not safe until further notice.

Update July 2017

I haven’t been paying attention to this issue – but a recent question on OTN left me searching for things I’d written about locks and I rediscovered the note and re-ran the basic tests on 11.2.0.4 this morning: the problem still exists.  I can get an exclusive lock on a table even though another session has an uncommitted insert on that table if I’ve disabled table locking on a child table (and that other session can carry on inserting rows into the table even though I hold an exclusive lock on it).

Later that day … I’ve re-run the tests on version 12.2.0.1 and the problem still exists there. I can insert rows into a table which is locked exclusive by another session if it’s the parent table in a referential integrity constraint and the child table has had table locks disabled.

Update June 2018

The bug number 21935698 supplied by Franck Pachot in the last comment below is reported as “Fixed in 19.1”, and gives as a workaround the instruction: “Make sure the enable/disable setting is the same on parent and child tables”.