ORA-00054 详解 父子表/子表间 互相影响

Cause:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Scenario: Testcase

From Session 1:

create table tmp_d (c1 number, c2 number);
alter table tmp_d add primary key (c1);
insert into tmp_d values (1, 1);

 
For this testcase >>  DO NOT COMMIT OR EXIT FROM SESSION 1


From Session 2:

create table tmp_f (c1 number, c2 number);
alter table tmp_f add constraint tmp_f_r1 foreign key (c1) references tmp_d (c1) disable novalidate;

---session 1 没有commit disable novalidate 是可以的，FK enable不可以。

--session 1 commit ，FK enable可以

--session1 主表没有主键，创建不了外键

--session 1主表有主键，创建外键后drop掉主键，外键一同消失

--session1  主表没有commit，主表ddl ora-054错误， dml要等待

--session2  子表没有commit，主表ddl ora-054错误， dml要等待

DML

如果delete 子表有的数据

ORA-02292: integrity constraint (SYSTEM.TMP_F_R1) violated - child record found

如果delete子表没有的数据，一直在等

--

alter system set ddl_lock_timeout=10 可以增加ORA-00054 timeout时间

DML 不行，要一直等待？？

-----------------------------两个子表互相影响，这个比较难找----破解 specify novalidate----------------

Adding a foreign key constraint to a child table can encounter "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" 
if DML is being performed on another child table
Creation of a foreign key is prevented on another child table of the same parent table until dml is complete.
Unless you specify novalidate, in which case the constraint is created and then the session waits on a lock.
For example

Session 1:

Create a parent table with a primary key
Create a child table
Add a foreign key constraint referencing the primary
Insert or a DML(UPDATE/DELETE) on a row into the child and do not commit

Session 2:

Create another child table
Add a foreign key constraint referencing the primary
  --- this fails with ORA-54

CAUSE
The cause of the error is that the un-committed DML on another child requires a TM lock on the parent to prevent it from being changed. 
When a new constraint is added to another child of the same parent, this addition also requires a TM lock , but this is prevented by the existing lock. 
  The session waits for the lock to be released and if this does not occur within a certain time period will raise ORA-54.

 
Example
-- Session 1

create table TEST1
( num1 VARCHAR2(2) not null, num2 VARCHAR2(50) not null, constraint PK_TEST1 primary key (num1))
organization index;  
insert into TEST1 (num1,num2) values ('1','TEST');
commit;

create table NUM_TEST1 ( user_num VARCHAR2(2));

alter table NUM_TEST1 add constraint FK_NUM1 foreign key (user_num) references TEST1 (num1);

insert into NUM_TEST1 (user_num) values ('1');

SQL> SELECT s.SID FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.audsid = userenv('SESSIONID') ;

      SID
----------
      191
NB it is irrelevant whether the Parent table is an IOT or not.

-- Session 2  (ddl_lock_timeout is reduced to speed up error generation)

alter session set ddl_lock_timeout=60;

create table NUM_TEST2 ( user_num VARCHAR2(2));

alter table NUM_TEST2 add constraint FK_num2 foreign key (user_num) references TEST1 (num1);
Session now waits and after 60 seconds, results in:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Having identified the process IDs of the two sessions (Session 1(SID 191) & Session 2(SID 10) ), these selects show the locks at the time:

QL> select * from dba_locks where session_id in ('191','10');

----dba_locks 在RAC下只看单个node的，不如gv$

SESSION_ID  LOCK_TYPE   MODE_HELD   MODE_REQUESTED LOCK_ID1 LOCK_ID2  LAST_CONVERT  BLOCKING_OTHERS

10          DML         None           Share        74839     0      8           Not Blocking
191         DML         Row-X (SX)     None         74839     0      277         Blocking
191         DML         Row-X (SX)     None         74841     0      277         Not Blocking
191         Transaction Exclusive      None         589852 1195      277         Not Blocking

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where SID in ('191','10');

      SID  TYPE     ID1                   ID2            LMODE         REQUEST               BLOCK
---------- ----    ----------             ---------   ----------      ----------            ----------
       10  TM      74839 (TEST1)            0             0                4                    0
      191  TM      74839 (TEST1)            0             3                0                    1
      191  TM      74841 (NUM_TEST1)        0             3                0                    0
      191  TX      589829                   1196          6                0                    0

6 rows selected.

SQL> select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
TEST@IN-ORACLE\SUNTYADA-IN ( SID=191 )  is blocking TEST@IN-ORACLE\SUNTYADA-IN ( SID=10 )

--RAC

SQL> select s1.username || '@' || s1.machine||' on node '||s1.INST_ID
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine ||' on node '||s2.INST_ID|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.INST_ID=s1.INST_ID and l2.INST_ID=s2.INST_ID
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

The un-committed insert in session 1 holds a TM Lock that is blocking other activity.

Object Identification:

SQL> SELECT s.SID FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.audsid = userenv('SESSIONID') ;

      SID
----------
       10

SQL> select object_name from dba_objects where object_id=74841;

OBJECT_NAME
-----------------------
NUM_TEST1

SQL> select object_name from dba_objects where object_id=74839;

OBJECT_NAME
-----------------------
TEST1 
Explanation:

Session 1(SID 191): This session holds locks on TEST1 and NUM_TEST1 in lock mode 3 (Row Exclusive) due to the uncommitted insert. It also holds an Exclusive (mode 6) TX  row lock for the inserted row.

Session 2(SID 10): On attempting to add the constraint, session 2 needs to acquire a share mode(TM mode 4) on parent table TEST1 , but since session 1 is already holding a TM lock in mode 3 , session 2 cannot get hold the lock. 

Why does session 2 require a shared mode on TEST1?

A foreign key constraint cannot be added without first checking the integrity of all rows in the parent table to satisfy the parent-child relationship
The parent table objects definition is expected to be static for the duration of the operation; there should be no modifications to the parent table while the check goes on in the metadata. Effectively the shared lock is saying "table definition is in use - cannot be changed now". It prevents DDL on the parent table.
Since Session 2 is unable to get this Shared lock on the parent, it waits for the Insert in session 1 to commit or rollback. So, until session 1 completes its transaction, session 2 will wait. This is done to maintain the Parent-Child referential integrity.

There is some discussion about locking in the following chapter, but all cases are not specifically covered:

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Concepts
Chapter 9 Data Concurrency and Consistency
http://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020
The documentation states:  "DML on a child table does not acquire a table lock on the parent table." , but this is referring to a eXclusive TX lock on the table that would prevent insertion of row data, whereas this case is a TM lock preventing the addition of a new constraint on a child.

SOLUTION
This is an expected behavior. Schedule the addition of constraints for periods of minimal activity

----------------------------------

select l.inst_id id, l.sid, s.serial#, s.username, s.machine, S.program, l.id1,
decode(l.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', l.lmode) lmode,
s.sql_id,sq.sql_fulltext, s.prev_sql_id, s.event, s.last_call_et
from gv$lock l, dba_objects o, gv$session s, gv$sqlarea sq
where o.object_id(+) = l.id1 and l.sid = s.sid and sq.SQL_ID(+)=s.SQL_ID  and l.inst_id = s.inst_id
and o.object_id  in (Select o.object_id
from dba_objects o
where object_name = 'TMP_D')
order by object_name, lmode;

select l.inst_id id, l.sid, s.serial#, s.username, s.machine, S.program, l.id1,
decode(l.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', l.lmode) lmode,o.OBJECT_NAME,
s.sql_id,sq.sql_fulltext, s.prev_sql_id, s.event, s.last_call_et
from gv$lock l, sys.dba_objects o, gv$session s, gv$sqlarea sq
where o.object_id(+) = l.id1 and l.sid = s.sid and sq.SQL_ID(+)=s.SQL_ID and sq.INST_ID(+)=s.INST_ID  and l.inst_id = s.inst_id 
--and o.object_id  in (Select o.object_id
--from dba_objects o
--where object_name = '&TMP_D')
order by object_name, lmode;
 

select T.status t_status, T.start_time, T.recursive "Recur", S.inst_id id, S.sid, S.serial#, S.username,
substr(s.osuser, 1, 15) osuser, substr(s.machine, 1, 15) machine, substr(s.program, 1, 15) program, s.status s_status,
T.used_urec "Urec", T.used_ublk "Ublk", s.sql_id
from gv$transaction T, gv$session S
where T.ses_addr = S.saddr and T.inst_id=S.inst_id
order by T.start_time;

Further Note:

Currently according to MOS NOTE: ID 1271688.1 –_fix_control is set to 5909305:OFF

 

SELECT * from gv$parameter where regexp_like(name, 'fix_control')