在外键上不创建索引，可能会引发死锁的问题。我将通过一些简单的案例，展示在不同情境下，索引的有无对锁状态的影响。
测试的数据库版本是11.2.0.2，所使用的表emp、dept与scott用户下的表相同。
测试1
~~~~~~~~~~~~~~~~~~~
情境：应用程序更新了主表的主键，但主键的键值上并没有真的被更改。没有索引创建在子表参照主表的外键的字段上。
session 1
===========
skydl@R11202(146,299)>insert into emp values(8001,’mike’,’ANALYST’,NULL,SYSDATE,8000,NULL,10);
1 row created.
session 2
============
skydl@R11202(125,43)>SELECT * FROM DEPT;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
skydl@R11202(125,43)>UPDATE DEPT SET DEPTNO=10,DNAME=’CCCC’ WHERE DEPTNO=10;
–挂起
session 3
================
–当第三个session试图插入时，被session2 阻塞
skydl@R11202(17,489)>insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20);
–挂起
select
mm.addr
, mm.kaddr
, mm.sid
, row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number
, mm.type
, mm.id1
, mm.id2
, decode(mm.lmode, 0, null, 1, ‘N’, 2, ‘SS|RS’, 3, ‘SX|RX’, 4, ‘S’, 5, ‘SSX|SRX’, 6, ‘X’) lmode
, decode(mm.request, 0, null, 1, ‘N’, 2, ‘SS|RS’, 3, ‘SX|RX’, 4, ‘S’, 5, ‘SSX|SRX’, 6, ‘X’) request
— , mm.ctime
, lpad(trunc(mm.ctime/60/60),3) || ‘ Hour ‘
|| lpad(to_char(trunc(mm.ctime/60) – trunc(mm.ctime/60/60) * 60,’fm09′), 2) || ‘ Min ‘
|| lpad(to_char(mm.ctime – trunc(mm.ctime/60)*60,’fm09′), 2) || ‘ Sec’ ctime
, case when mm.block = 1 and mm.lmode != 0 then ‘holder’
when mm.block = 0 and mm.request != 0 then ‘waiter’
else null end role
, case when ee.blocking_session is not null then ‘waiting for SID ‘|| ee.blocking_session else null end blocking_session
, dd.sql_text sql_text
, cc.event wait_event
from
v$lock mm
, v$session ee
, v$sqlarea dd
, v$session_wait cc
where mm.sid in (
select nn.sid
from (
select
tt.*
, count(1) over (partition by tt.type,tt.id1,tt.id2) cnt
, max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag
, max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag
from v$lock tt ) nn
where nn.cnt > 1
and nn.lmod_flag != 0
and nn.request_flag != 0)
and mm.sid = ee.sid(+)
and ee.sql_id = dd.sql_id(+)
and mm.sid = cc.sid(+)
order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
ADDR KADDR SID RESOURCE_ROW_NUMBER TYPE ID1 ID2 LMODE REQUEST CTIME ROLE BLOCKING_SESSION SQL_TEXT WAIT_EVENT
—————- —————- ———- ——————- —- ———- ———- ——- ——- ———————- —— ——————————————————– —————————————————————————- ——————————
00000000774C4450 00000000774C44A8 146 1 AE 100 0 S 0 Hour 06 Min 11 Sec SQL*Net message from client
00000000774C47D0 00000000774C4828 125 2 AE 100 0 S 0 Hour 05 Min 31 Sec waiting for SID 146 UPDATE DEPT SET DEPTNO=10,DNAME=’CCCC’ WHERE DEPTNO=10 enq: TM – contention
00000000774C5508 00000000774C5560 17 3 AE 100 0 S 0 Hour 01 Min 45 Sec waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
00007FC99FCC9200 00007FC99FCC9260 146 1 TM 78423 0 SX|RX 0 Hour 03 Min 55 Sec SQL*Net message from client
00007FC99FCC9200 00007FC99FCC9260 125 2 TM 78423 0 SX|RX 0 Hour 02 Min 46 Sec waiting for SID 146 UPDATE DEPT SET DEPTNO=10,DNAME=’CCCC’ WHERE DEPTNO=10 enq: TM – contention
00007FC99FCC9200 00007FC99FCC9260 17 3 TM 78423 0 SX|RX 0 Hour 01 Min 11 Sec waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
00007FC99FCC9200 00007FC99FCC9260 146 1 TM 78424 0 SX|RX 0 Hour 03 Min 55 Sec holder SQL*Net message from client
00007FC99FCC9200 00007FC99FCC9260 125 2 TM 78424 0 S 0 Hour 02 Min 46 Sec waiter waiting for SID 146 UPDATE DEPT SET DEPTNO=10,DNAME=’CCCC’ WHERE DEPTNO=10 enq: TM – contention
00007FC99FCC9200 00007FC99FCC9260 17 3 TM 78424 0 SX|RX 0 Hour 01 Min 11 Sec waiter waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
000000007631D160 000000007631D1D8 146 1 TX 131082 14833 X 0 Hour 03 Min 55 Sec SQL*Net message from client
选定了 10 行
分析：通过上面的测试我们可以看到，session 2在更新主键的时候，因为在子表emp对应的外键字段上没有锁，因此需要在表级（TM）追加了一个S模式的锁。
session2 请求追加S模式的锁在了TM上，因为session 1正在做DML处理，对于DML处理会在TM上加上SX模式的锁，因此SX与S模式的锁是互斥的，因此session 2
被阻塞而挂起，session 3需要做DML处理，同样需要请求SX模式的锁在TM上，因此它被session 2 在TM上S模式锁的请求阻塞。对于并发DML session之间，
TM上SX模式的锁是共享的。
测试2
~~~~~~~~~~~~~~~~~~~
情境：删除主表的一行。没有索引创建在子表参照主表的外键的字段上。在子表上追加了带有“on delete cascade”子句的外键约束
alter table emp drop constraint emp_fk;
alter table emp add constraint emp_fk foreign key (deptno) references dept(deptno) on delete cascade;
session 1
===========
skydl@R11202(146,299)>insert into emp values(8001,’mike’,’ANALYST’,NULL,SYSDATE,8000,NULL,10);
1 row created.
session 2
============
skydl@R11202(125,43)>delete from dept where deptno=10;
–挂起
session 3
===========
skydl@R11202(17,489)>insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20);
–挂起
select
mm.addr
, mm.kaddr
, mm.sid
, row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number
, mm.type
, mm.id1
, mm.id2
, decode(mm.lmode, 0, null, 1, ‘N’, 2, ‘SS|RS’, 3, ‘SX|RX’, 4, ‘S’, 5, ‘SSX|SRX’, 6, ‘X’) lmode
, decode(mm.request, 0, null, 1, ‘N’, 2, ‘SS|RS’, 3, ‘SX|RX’, 4, ‘S’, 5, ‘SSX|SRX’, 6, ‘X’) request
— , mm.ctime
, lpad(trunc(mm.ctime/60/60),3) || ‘ Hour ‘
|| lpad(to_char(trunc(mm.ctime/60) – trunc(mm.ctime/60/60) * 60,’fm09′), 2) || ‘ Min ‘
|| lpad(to_char(mm.ctime – trunc(mm.ctime/60)*60,’fm09′), 2) || ‘ Sec’ ctime
, case when mm.block = 1 and mm.lmode != 0 then ‘holder’
when mm.block = 0 and mm.request != 0 then ‘waiter’
else null end role
, case when ee.blocking_session is not null then ‘waiting for SID ‘|| ee.blocking_session else null end blocking_session
, dd.sql_text sql_text
, cc.event wait_event
from
v$lock mm
, v$session ee
, v$sqlarea dd
, v$session_wait cc
where mm.sid in (
select nn.sid
from (
select
tt.*
, count(1) over (partition by tt.type,tt.id1,tt.id2) cnt
, max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag
, max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag
from v$lock tt ) nn
where nn.cnt > 1
and nn.lmod_flag != 0
and nn.request_flag != 0)
and mm.sid = ee.sid(+)
and ee.sql_id = dd.sql_id(+)
and mm.sid = cc.sid(+)
order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
ADDR KADDR SID RESOURCE_ROW_NUMBER TYPE ID1 ID2 LMODE REQUEST CTIME ROLE BLOCKING_SESSION SQL_TEXT WAIT_EVENT
—————- —————- ———- ——————- —- ———- ———- ——- ——- ———————- —— ——————————————————– —————————————————————————- ——————————
00000000774C4450 00000000774C44A8 146 1 AE 100 0 S 0 Hour 17 Min 03 Sec SQL*Net message from client
00000000774C47D0 00000000774C4828 125 2 AE 100 0 S 0 Hour 16 Min 23 Sec waiting for SID 146 delete from dept where deptno=10 enq: TM – contention
00000000774C5508 00000000774C5560 17 3 AE 100 0 S 0 Hour 12 Min 37 Sec waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
00007FC9A01E5280 00007FC9A01E52E0 146 1 TM 78423 0 SX|RX 0 Hour 01 Min 41 Sec SQL*Net message from client
00007FC9A01E5280 00007FC9A01E52E0 125 2 TM 78423 0 SX|RX 0 Hour 01 Min 12 Sec waiting for SID 146 delete from dept where deptno=10 enq: TM – contention
00007FC9A01E5280 00007FC9A01E52E0 17 3 TM 78423 0 SX|RX 0 Hour 00 Min 38 Sec waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
00007FC9A01E5280 00007FC9A01E52E0 146 1 TM 78424 0 SX|RX 0 Hour 01 Min 41 Sec holder SQL*Net message from client
00007FC9A01E5280 00007FC9A01E52E0 125 2 TM 78424 0 SSX|SRX 0 Hour 01 Min 12 Sec waiter waiting for SID 146 delete from dept where deptno=10 enq: TM – contention
00007FC9A01E5280 00007FC9A01E52E0 17 3 TM 78424 0 SX|RX 0 Hour 00 Min 38 Sec waiter waiting for SID 125 insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20) enq: TM – contention
000000007631D160 000000007631D1D8 146 1 TX 524308 14777 X 0 Hour 01 Min 41 Sec SQL*Net message from client
选定了 10 行
select owner,object_name,object_type,object_id from dba_objects where object_id in (78423,78424);
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
——- ————— ——————- ———-
SKYDL EMP TABLE 78424
SKYDL DEPT TABLE 78423
session 1
==============
skydl@R11202(146,299)>rollback;
Rollback complete.
session 2
=============
skydl@R11202(125,43)>delete from dept where deptno=10;
— 仍然挂起
session 3
=============
skydl@R11202(17,489)>insert into emp values(8002,’mike2′,’ANALYST’,NULL,SYSDATE,8000,NULL,20);
1 row created.
ADDR KADDR SID RESOURCE_ROW_NUMBER TYPE ID1 ID2 LMODE REQUEST CTIME ROLE BLOCKING_SESSION SQL_TEXT WAIT_EVENT
—————- —————- ———- ——————- —- ———- ———- ——- ——- ———————- —— ——————————————————– ———————————— —————————-
00000000774C47D0 00000000774C4828 125 1 AE 100 0 S 0 Hour 27 Min 56 Sec waiting for SID 17 delete from dept where deptno=10 enq: TM – contention
00000000774C5508 00000000774C5560 17 2 AE 100 0 S 0 Hour 24 Min 10 Sec SQL*Net message from client
00007FC99FCF42E0 00007FC99FCF4340 125 1 TM 78423 0 SX|RX 0 Hour 12 Min 45 Sec waiting for SID 17 delete from dept where deptno=10 enq: TM – contention
00007FC99FCF42E0 00007FC99FCF4340 17 2 TM 78423 0 SX|RX 0 Hour 12 Min 11 Sec SQL*Net message from client
00007FC99FCF42E0 00007FC99FCF4340 17 1 TM 78424 0 SX|RX 0 Hour 03 Min 27 Sec holder SQL*Net message from client
00007FC99FCF42E0 00007FC99FCF4340 125 2 TM 78424 0 SX|RX SSX|SRX 0 Hour 03 Min 27 Sec holder waiting for SID 17 delete from dept where deptno=10 enq: TM – contention
000000007631DB70 000000007631DBE8 125 1 TX 262155 12959 X 0 Hour 03 Min 27 Sec waiting for SID 17 delete from dept where deptno=10 enq: TM – contention
000000007630DDD8 000000007630DE50 17 1 TX 589837 14822 X 0 Hour 03 Min 27 Sec SQL*Net message from client
选定了 8 行
session3
============
skydl@R11202(17,489)>rollback;
Rollback complete.
session2
==============
skydl@R11202(125,43)>delete from dept where deptno=10;
1 row deleted.
分析：和第一个测试相比，session 2请求在TM上加上SSX模式的锁。因为外键约束带有“on delete cascade”子句，即使在session 1 回滚后，
session 2仍然挂起，因为session 3 在表emp上仍然有active的事务处理。在session 3 回滚之后，session 2 才得以继续进行。
测试3
~~~~~~~~~~~~~~~~~~~
情境：应用程序更新了主表的主键，但主键的键值上并没有真的被更改。有索引创建在子表参照主表的外键的字段上。
create index emp_idx2 on emp(deptno);
session 1
===========
skydl@R11202(146,299)>insert into emp values(8001,’mike’,’ANALYST’,NULL,SYSDATE,8000,NULL,10);
1 row created.
session 2
============
skydl@R11202(125,43)>UPDATE DEPT SET DEPTNO=10,DNAME=’CCCC’ WHERE DEPTNO=10;
1 row updated.
分析： 此时session 2没有被session 1 锁阻塞
总结：
1. 应该在所有的外键上创建索引，避免不必要的死锁产生。可以利用下面的sql定位：
—查询主表对应的哪些子表上的外键没有创建索引
select *
from dba_cons_columns bb
where exists(
select null
from (
select *
from dba_constraints aa
start with table_name = ‘<主表名>’ and owner='<主表owner>’
connect by prior constraint_name=r_constraint_name) aa
where aa.owner =bb.owner
and aa.constraint_name=bb.constraint_name)
and table_name != ‘<主表名>’
and not exists(
select null
from dba_ind_columns cc
where cc.table_owner = bb.owner
and cc. table_name=bb.table_name
and cc.column_name= bb.column_name
);
2. 如果应用相关于update 主表的语句，尽量避免更新主键
3.应用程序设计上，应额外注意在子表上追加了带有“on delete cascade”子句的外键约束产生锁的情况。
为什么我们应在外键上创建索引
Authors