oracle死锁原因分析

 

死锁的原因


1。模拟死锁

1。1。主表

-- Create table
create table WDZ1
(
WDZ1ID NUMBER not null,
MEMO VARCHAR2(20)
)
;
alter table WDZ1
add constraint XXXXXX primary key (WDZ1ID);

1。2。从表（没有外健的索引）


-- Create table
create table WDZ2
(
WDZ2ID NUMBER not null,
WDZ1ID NUMBER,
MEMO VARCHAR2(20)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table WDZ2
add constraint XXXXX primary key (WDZ2ID)
;
alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID);
1。3。插入数据表到住表

begin
insert into wdz1 values (1,'aa');
insert into wdz1 values(2,'aa2');
insert into wdz1 values (3,'aa3');

insert into wdz2 values(10,3,'wdz3--1');
commit;
end;

1。4。在一个数据库seeesion里面插入数到从表，但是不提交事务

begin
update wdz2 set memo='update wdz2 momo'

where wdz2id=10;

insert into wdz2 values(20,2,'wdz2--1');

end;


对从表进行插入/修改记录，施加的锁也就是行级锁

1。5。在另外一个数据库seeesion里面删除 主表数据

delete from wdz1 where wdz1id=1

这时候 程序会死锁，除非 上面的 对从表的 数据操作提交事务或者回滚事务。

2。具体原因分析

一个数据表的外键主要有3种方式来维护它自己和主表数据的一致性。

(1)delete cascade

例子如下：

alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID) on delete cascade;

(2)Set null

例子如下：

alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID) on delete set null;

(3)No action

注意，这是oracle外键使用时候的默认选项。

例子如下：

alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID);

以前出现死锁主要是我们认为，在 1。4。会对数据表wdz2进行施加行级锁，但是从表(wdz2)的外健是No action，删除主表(wdz1)不会去访问从表，更不会去锁定 wdz2表的记录或者对整个数据从表(wdz2)施加表级锁。事实上 oralce的No action 选项的字面意思欺骗了我们，oracle在删除主表的时候

会去寻找所有以主表的主键作为外键的数据表，然后看去看从表

是否有该外键的索引，如果没有则会对整个从表施加表级锁，然后

对从表进行全表扫描。当然如果从表存在外键的索引，会去访问

对应的索引，而不会对从表本身进行加锁。

3。解决办法
为从表的外键建立索引，这也是在使用外键的推荐方式。
这样就能避免上面出现的问题了。

这只是锁等待，而不是死锁，死锁oracle回自动处理的

相关资料 如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁。 预备知识： DDL(DATABASE DEFINITION LANGUAGE)：数据库定义语言，如create table、drop table..... DML(DATABASE MODIFICATION LANGUAGE):数据库修改语言，如insert、delete、update...... 参考资料：Oracle8 Administrator's Guide, Release 8.0 Oracle8 Tuning, Release 8.0 ORACLE锁具体分为以下几类： 1.按用户与系统划分，可以分为自动锁与显示锁 自动锁：当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。 显示锁：某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。 2.按锁级别划分，可分为共享锁与排它锁 共享锁：共享锁使一个事务对特定数据库资源进行共享访问——另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性，但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。 排它锁：事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。 3.按操作划分，可分为DML锁、DDL锁 +DML锁又可以分为，行锁、表锁、死锁 -行锁：当事务执行数据库插入、更新、删除操作时，该事务自动获得操作 表中操作行的排它锁。 -表级锁：当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以 防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行 过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语 句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用 LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参 考相关文档)。 -死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就 出现死锁。 如事务1在表A行记录#3中有一排它锁，并等待事务2在表A中记录#4 中排它锁的释放，而事务2在表A记录行#4中有一排它锁，并等待事务 1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造 成了死锁。死锁一般是因拙劣的事务设计而产生。 死锁只能使用SQL下:alter system kill session 'sid,serial#'； 或者使用相关操作系统kill进程的命令，如UNIX下kill -9 sid,或者 使用其它工具杀掉死锁进程。 +DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁 -排它DDL锁：创建、修改、删除一个数据库对象的DDL语句获得操作对象的 排它锁。 如使用alter table语句时，为了维护数据的完成性、一致性、 合法性，该事务获得一排它DDL锁。 -共享DDL锁：需在数据库对象之间建立相互依赖关系的DDL语句通常需共享 获得DDL锁。 如创建一个包，该包中的过程与函数引用了不同的数据库表， 当编译此包时，该事务就获得了引用表的共享DDL锁。 -分析锁：ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使 运行相同语句的应用速度更快。一个在共享池中缓存的对象获得 它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型， ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖 关系。当一个事务修改或删除了共享池持有分析锁的数据库对象 时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语 句时，ORACLE重新分析编译此语句。 4.内部闩锁 内部闩锁：这是ORACLE中的一种特殊锁，用于顺序访问内部系统结构。 当事务需向缓冲区写入信息时，为了使用此块内存区域， ORACLE首先必须取得这块内存区域的闩锁，才能向此块内存写入 信息。 文章选项： 正如上面的实验，互锁或死锁时，ORACLE已经解除了这种锁，所以只要等待某一占有资源的进程打开锁（通过ROLLBACK/COMMIT)即可。 对于非正常终止进程占有的锁，把握以前在内部网站上法的一片贴子转贴在下面： 锁等待的诊断及排除 ===================================== 在ORACLE中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。 当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。 但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现"死机"，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。 这时，我们需要迅速地诊断出锁住资源的用户并解决其锁定。 1. 诊断系统中的锁 为了找出系统中那些用户锁住资源以及那些用户在等待相应的资源，可使用以下语句(其中的/*+ NO_MERGE(..) */千万不可省略, 否则会很慢)： -- looklock.sql -- use the NO_MERGE hints can speed up the query select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b, v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value union select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b, v$sqltext c where b.id1 in (select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1 from v$session d, v$lock e where d.lockwait = e.kaddr) and a.username is not null and a.sid = b.sid and b.request=0 and c.hash_value =a.sql_hash_value; 执行后的结果如下所示： Stat USERNAME MACHINE SID SERIAL# Seconds ID1 ---- ------------------------------ ---------------- --------- --------- --------- --------- SQL ---------------------------------------------------------------- Lock CIQUSR CIQ/DULMACER 12 966 245 131089 select * from c_trade_mode for update Wait CIQUSR CIQ/DULMACER 10 735 111 131089 update c_trade_mode set x_name = 'zzz' where x_code='5' Wait CIQUSR CIQ/DULMACER 15 106 1094 131089 select * from c_trade_mode for update 其中： Status有两种状态，LOCK表明该进程锁住了某个资源，WAIT表示该进程正在等待某个资源。 Username, Machine分别为ORACLE用户名及机器名 SID，SERIAL#可用于随后的解锁操作 Seconds表示该进程最后一次进行操作至当前的时间(秒) ID1, 锁标识。某个LOCK状态的ID1与某个WAIT状态的ID1相同，可说明锁的正是另一个进程等待的。 SQL: 锁住资源的SQL语句 2. 解除锁 诊断出锁的状态后，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为非正常操作，即，其状态为"inactive"，且其Seconds已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。 alter system kill session 'sid, serial#'; 例如: 对于上例中显示的结果, 可用以下语句清除锁住资源的进程: alter system kill session '12, 966'; 关于你所说：在网络断掉（通过拔掉网线）或非正常终止进程（通过task manager强行关闭sql*plus)时，oracle在有限的时间内（我只观查了5-10分）内，oracle未能对该进程作任何处理。 这个处理与TCP协议有关，因为SQL NET在使用TCP/IP协议进行网络连接时是一种短连接,当ORACLE连接异常终止时，因为是异常终止，终止信号并没有通过网络通知server端，因此只有下次server有结果从服务器端返回需与client通信时，server才会发现此client已经端掉。因此出现你前面所提ORACLE处理异常终止进程延时情况. 死锁：你可以试验一条彼此存在依赖关系的update语句，ORACLE处理这种锁时不是很好。 查锁语句：查询产生锁的用户锁sql select a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltext from v$session a, v$lock b, v$sqltext c where b.id1 in 　　 (select distinct e.id1 　　 from v$session d, v$lock e 　　 where d.lockwait = e.kaddr) 　　 and a.sid = b.sid 　　 and c.hash_value = a.sql_hash_value 　　 and b.request = 0; 死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就 出现死锁。 如事务1在表A行记录#3中有一排它锁，并等待事务2在表A中记录#4 中排它锁的释放，而事务2在表A记录行#4中有一排它锁，并等待事务 1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造 成了死锁。死锁一般是因拙劣的事务设计而产生