由于外键约束问题导致ORA-00060错误，报Single resource deadlock

由于外键约束问题导致ORA-00060错误，报Single resource deadlock [TM]处理过程

这个问题持续很久，当时看到global_enqueue_deadlock，没有多看，直接认为是全局死锁，需要业务自己协调，从trace文件中拿来一个delete的SQL

但是后来偶然机会让辅助监控的工程师负责再看看该问题，确发现是另一回事。

当时的trace文件内容如下

死锁描述：

Single resource deadlock: blocking enqueue which blocks itself, f 0

资源名称：

----------resource 0xa0732f80----------------------

resname : [0x84584][0x0],[TM][ext 0x0,0x0][domid 0x0]

也就是在资源0x84584上有死锁发生，0x84584对应十进制是542084，对应的对象名称是test2_abc。

从TRACE文件中可以看到正在执行的语句如下：

delete from user123.test2 where ID_=:1

经过进一步确认二者关系，表test2_abc的外键引用了表test2的主键，但是外键没有索引

表定义如下

CREATE TABLE "user123"."test2_abc"

( "ID_" NUMBER(19,0) NOT NULL ENABLE,

"TASK_ID_" NUMBER(19,0) NOT NULL ENABLE,

"USER_" VARCHAR2(60 CHAR),

"USER_NAME_" VARCHAR2(60 CHAR),

PRIMARY KEY ("ID_")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "user123" ENABLE,

CONSTRAINT "FK_MAYFQQCA51WHMHQQ1MDLP8QG6" FOREIGN KEY ("TASK_ID_")

REFERENCES "user123"."test2" ("ID_") ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "user123"

TASK_ID_字段是外键字段引用了表test2的ID_字段。

表上索引列情况如下：

select index_owner,index_name,column_name,column_position from dba_ind_columns where table_name='test2_abc' and table_owner='user123';

输出内容如下：

INDEX_OWNER

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

INDEX_NAME

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

COLUMN_NAME

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

COLUMN_POSITION

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

user123

SYS_C00428263

ID_

表上只有一个索引SYS_C00428263在ID_列上，在task_id_无索引

解决方案：在外键创建索引，其实Oracle建议在所有外键上都要创建索引，以避免性能问题。参考MOS文档473124.1

来自 “ 开源世界 ” ，链接：http://ym.baisou.ltd/?id=512，如需转载，请注明出处，否则将追究法律责任。