本文主要是介绍Oracle Foreign key 无索引导致的死锁 deadlock 或者hang，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
---------------lock
死锁通常发生在主表和子表更新主外键上。更新主表的主键，那么子表的外键会被锁住
如果删除主表的行，那么子表会被锁住。
create table p(x int primary key);
create table c( x references p);
insert into p values(1);
insert into p values (2);
commit;
在一个seesion下执行
insert into c values(2);
换一个session执行
SQL> delete from p where x = 1;
会发现hang住了。
select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) || nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) || nvl2(cname8, ',' || cname8, null)
columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1,
cname2,
cname3,
cname4,
cname5,
cname6,
cname7,
cname8)
and i.column_position <= cons.col_cnt
group by i.index_name
)
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住了。
---------------------------dead lock
INSERT
Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。
UPDATE 和DELETE当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。
Select …for update
当一个用户发出select..for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。
可以通过发出 select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with nowait specified.
select… for update wait 100；设置等待时间
死锁-deadlock
定义:当两个用户希望持有对方的资源时就会发生死锁.
即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.
例子：
1：用户1对A表进行Update，没有提交。
2：用户2对B表进行Update，没有提交。
此时不存在资源共享的问题。
3：如果用户2此时对A表作update,则会发生阻塞，需要等到用户一的事物结束。
4：如果此时用户1又对B表作update，则产生死锁。此时Oracle会选择其中一个用户进行会滚，使另一个用户继续执行操作。
起因:
Oracle的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。
drop table p cascade constraints;
drop table c;
create table p(x int primary key);
create table c( x references p);
insert into p values(1);
insert into p values (2);
insert into c values (2);
commit;
---session 1
SQL> select * from p ;
X
---------------------------------------
1
2
SQL> select *from c;
X
---------------------------------------
2
SQL>
SQL> update p set x=3 where x=1
2 ;
1 row updated
SQL> update c set x=3 where x=2;-------主外键存在情况，只要三个update 就行
1 row updated
SQL>
SQL> select * from p ;
X
---------------------------------------
2
3
SQL> select *from c;
X
---------------------------------------
3
SQL>
---session 2
SQL>
SQL> select * from p ;
X
---------------------------------------
1
2
SQL> select *from c;
X
---------------------------------------
2
SQL> update c set x=1 where x=2;
update c set x=1 where x=2
ORA-00060: deadlock detected while waiting for resource
SQL>
SQL> select * from p ;
X
---------------------------------------
2
3
SQL> select *from c;
X
---------------------------------------
3
SQL>
----没有主外键--------------deadlock 发生在session 1--P的update 成功了
---session 1
SQL> select * from p ;
X
---------------------------------------
1
2
SQL> select *from c;
X
---------------------------------------
2
SQL> update p set x=3 where x=1;
1 row updated
SQL> update c set x=3 where x=2;
update c set x=3 where x=2
ORA-00060: deadlock detected while waiting for resource
SQL>
SQL>
SQL> select * from p ;
SQL>
SQL> select * from p ;
X
---------------------------------------
3
2
SQL> select *from c;
X
---------------------------------------
2 ---如果session 2 未commit还是2
SQL> commit;
Commit complete
SQL> select *from c;
X
---------------------------------------
3
SQL>
---session 2
SQL>
SQL> select * from p ;
X
---------------------------------------
1
2
SQL> select *from c;
X
---------------------------------------
2
SQL> update c set x=3 where x=2;
1 row updated----立即成功
SQL> update p set x=3 where x=1;
SQL>
0 rows updated----等待session commit， 如果commit 这边立即开始执行，记录没有了
SQL> select *from c;
X
---------------------------------------
3------这边是3
SQL> select * from p ;
X
---------------------------------------
3
2
SQL>
这篇关于Oracle Foreign key 无索引导致的死锁 deadlock 或者hang的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
