oracle怎么deadlock,Oracle Deadlock

死锁通常发生在主表和子表更新主外键上。更新主表的主键，那么子表的外键会被锁住

如果删除主表的行，那么子表会被锁住。

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住了。