Oracle 锁问题 - ORA-00054: 资源繁忙 - 创建外键时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31860875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle lock issue - ORA-00054: resource busy - while creating a foreign key
提问by rt15
Initial situation:
初始情况：
- A table PARENT_TABLE with a primary key on its column PK_COL.
- A table CHILD_TABLE1 with a foreign key on PARENT_TABLE(PK_COL).
- 表 PARENT_TABLE 在其列 PK_COL 上具有主键。
- 在 PARENT_TABLE(PK_COL) 上带有外键的表 CHILD_TABLE1。
I insert a line into CHILD_TABLE1 in a transaction and do not commit.
我在事务中的 CHILD_TABLE1 中插入一行，但没有提交。
Then I try to create a table CHILD_TABLE2 symmetrical to CHILD_TABLE1 in another session.
But an ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredis raised when I create the foreign key, because of the ongoing insertion in CHILD_TABLE1.
然后我尝试在另一个会话中创建一个与 CHILD_TABLE1 对称的表 CHILD_TABLE2。但是ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired当我创建外键时会引发，因为在 CHILD_TABLE1 中正在进行插入。
I don't understand why Oracle is preventing the foreign key creation: there is no modification performed on PARENT_TABLE.
我不明白为什么 Oracle 阻止创建外键：没有对 PARENT_TABLE 执行任何修改。
Please help.
请帮忙。
To reproduce under sqlplus:
在 sqlplus 下重现：
set autocommit off
create table PARENT_TABLE(PK_COL varchar(10));
alter table PARENT_TABLE add constraint PK_CONSTRAINT primary key (PK_COL);
insert into PARENT_TABLE values ('foo');
commit;
create table CHILD_TABLE1(CHILD_PK_COL varchar(10), FK_COL varchar(10));
alter table CHILD_TABLE1 add constraint CHILD_TABLE1_CONSTRAINT foreign key (FK_COL) references PARENT_TABLE(PK_COL);
create index CHILD_TABLE1_INDEX on CHILD_TABLE1(FK_COL);
insert into CHILD_TABLE1 values ('bar', 'foo');
In another console:
在另一个控制台中：
alter session set ddl_lock_timeout=10;
create table CHILD_TABLE2(CHILD_PK_COL varchar(10), FK_COL varchar(10));
alter table CHILD_TABLE2 add constraint CHILD_TABLE2_CONSTRAINT foreign key (FK_COL) references PARENT_TABLE(PK_COL);
Funny: with NOVALIDATE in CHILD_TABLE2_CONSTRAINT creation, the execution is hanging...
有趣：在 CHILD_TABLE2_CONSTRAINT 创建中使用 NOVALIDATE，执行挂起......
回答by Maheswaran Ravisankar
You are not modifying something in the parent table. But you're actually, trying to refer its primary key in your child table. Before establishing a relationship or any
DDLwith table, it has to be free of locks.
您没有修改父表中的内容。但实际上，您正在尝试在子表中引用其主键。在建立关系或任何
DDL与表的关系之前，它必须是无锁的。
So, before creating this constraint, Oracle do check for existing locks over the referred table(PARENT_TABLE). A lock over a table(Table Level Lock,in this context) is actually for a reason to adhere to the ACIDproperties.
因此，在创建此约束之前，Oracle 会检查引用的 table( PARENT_TABLE) 上的现有锁。表上的锁（在此上下文中为表级锁）实际上是出于遵守ACID属性的原因。
One best example to understand its importance is ON DELETE CASCADEwhich means if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
理解其重要性的一个最好例子是ON DELETE CASCADE，如果父表中的记录被删除，那么子表中的相应记录将被自动删除。
So, when there's a uncommitted insert/update/delete over the child table referring a parent table. No other referential constraint can be created to the parent. Just to avoid a deadlock or chaos.
因此，当在引用父表的子表上存在未提交的插入/更新/删除时。不能为父级创建其他引用约束。只是为了避免僵局或混乱。
To be more crisp, when you have an uncommitted insert in your child table. There's a lock over your parent table as well. So all other further DDLs referring it will be made wait.
更明确地说，当您的子表中有未提交的插入时。您的父表也有锁定。因此，所有其他引用它的 DDL 将被等待。
You can use this query to check the same.
您可以使用此查询来检查相同的内容。
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a ,
v$session b,
dba_objects c
WHERE b.sid = a.session_id
AND a.object_id = c.object_id;
回答by rt15
I added the LOCKED_MODE explanation in you query:
我在您的查询中添加了 LOCKED_MODE 解释：
DECODE(a.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE (RS/SS)', 3,'ROW EXCLUSIVE (RX/SX)', 4,'SHARE (S)', 5,'SHARE ROW EXCLUSIVE (SRX/SSX)', 6,'EXCLUSIVE (X)', NULL) LOCK_MODE.
Here is the result:
结果如下：
OBJECT_NAME OBJECT_TYPE LOCK_MODE SID SERIAL# STATUS
------------------------------ ------------------- ----------------------------- ---------- ---------- --------
PARENT_TABLE TABLE ROW EXCLUSIVE (RX/SX) 71 8694 INACTIVE
CHILD_TABLE1 TABLE ROW EXCLUSIVE (RX/SX) 71 8694 INACTIVE
RX/SX is a table lock so it prevents any DDL operation (That seems to be said in the doc). This lock is used on both parent and child. I suppose that the lock is added on parent to at least prevent it from being deleted so we would lost the pending update on the child table.
RX/SX 是一个表锁，因此它可以防止任何 DDL 操作（这似乎在文档中说）。此锁用于父和子。我想在父表上添加锁至少是为了防止它被删除，所以我们会丢失子表上的挂起更新。
That said, I still have no solution. Suppose that the parent table is a manufacturer. There is a child cartable and we are inserting plenty of new cars in that table on the fly. There is a foreign key from carto manufacturer. Now there is a new product that we want to manage: "bicycles". So we want to create a bicycletable similar to car. But we cannot create the table as we are performing insertions in car. Seems a very simple use case... How to support it?
也就是说，我仍然没有解决方案。假设父表是制造商。有一个儿童汽车桌，我们正在那个桌子上快速插入大量新车。从汽车到制造商有一个外键。现在有一个我们想要管理的新产品：“自行车”。所以我们想创建一个类似于car的自行车桌。但是我们无法创建表，因为我们在car中执行插入。看起来很简单的用例……如何支持？
===== Edit: There might be no solution. Here is a guy with the same issue.
===== 编辑：可能没有解决方案。这是一个有同样问题的人。
