死锁通常发生在主表和子表更新主外键上。更新主表的主键,那么子表的外键会被锁住
如果删除主表的行,那么子表会被锁住。
SQL> create table p(x int primary key);
Table created.
SQL> create table c( x references p);
Table created.
SQL> insert into p values(1);
1 row created.
SQL> insert into p values (2);
1 row created.
SQL> commit;
Commit complete.
在一个seesion下执行
SQL> insert into c values(2);
1 row created.
换一个session执行
SQL> delete from p where x = 1;
会发现hang住了。
SQL> column columns format a30word_wrapped
SQL> column columns format a15word_wrapped
SQL> column constraint_name format a15word_wrapped
SQL>
SQL> select table_name, constraint_name,
2 cname1 ||nvl2(cname2,','||cname2,null) ||
3 nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
4 nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
5 nvl2(cname7,','|| cname7,null)|| nvl2(cname8,','||cname8,null)
6 columns
7 from ( select b.table_name,
8 b.constraint_name,
9 max(decode(position,1,column_name,null)) cname1,
10 max(decode(position,2,column_name,null)) cname2,
11 max(decode(position,3,column_name,null)) cname3,
12 max(decode(position,4,column_name,null)) cname4,
13 max(decode(position,5,column_name,null)) cname5,
14 max(decode(position,6,column_name,null)) cname6,
15 max(decode(position,7,column_name,null)) cname7,
16 max(decode(position,8,column_name,null)) cname8,
17 count(*) col_cnt
18 from ( select substr(table_name,1,30) table_name,
19 substr(constraint_name,1,30)constraint_name,
20 substr(column_name,1,30)column_name,
21 position
22 from user_cons_columns ) a,
23 user_constraints b
24 where a.constraint_name = b.constraint_name
25 and b.constraint_type ='R'
26 group by b.table_name,b.constraint_name
27 ) cons
28 where col_cnt > ALL
29 (select count(*)
30 from user_ind_columns i
31 where i.table_name = cons.table_name
32 and i.column_name in (cname1,cname2,cname3,cname4,
33 cname5,cname6,cname7,cname8)
34 and i.column_position <= cons.col_cnt
35 group by i.index_name
36 )
37 /
TABLE_NAME CONSTRAINT_NAME COLUMNS
--------------------------------------------- ---------------
C SYS_C007722 X
发现C上没有索引,这个问题可以通过创建索引来解决
SQL> create index idx_c on c(x);
Index created.
SQL> insert into c values(2);
1 row created
换个session2执行
SQL> delete from p where x = 1;
1 row deleted.
可以 看到不再hang住了。