oracle死锁原因分析.doc

oracle死锁原因分析死锁的原因1。模拟死锁1。1。主表- Create tablecreate table WDZ1(WDZ1ID NUMBER not null,MEMO VARCHAR2(20);alter table WDZ1add constraint XXXXXX primary key (WDZ1ID);1。2。从表（没有外健的索引）- Create tablecreate table WDZ2(WDZ2ID NUMBER not null,WDZ1ID NUMBER,MEMO VARCHAR2(20);- Create/Recreate primary, unique and foreign key constraints alter table WDZ2add constraint XXXXX primary key (WDZ2ID);alter table WDZ2add constraint XXX foreign key (WDZ1ID)references WDZ1 (WDZ1ID);1。3。插入数据表到住表begininsertinto wdz1 values (1,aa);insertinto wdz1 values(2,aa2);insertinto wdz1 values (3,aa3);insert into wdz2 values(10,3,wdz3-1);commit;end;1。4。在一个数据库seeesion里面插入数到从表，但是不提交事务beginupdate wdz2set 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 WDZ2add constraint XXX foreign key (WDZ1ID)references WDZ1 (WDZ1ID) on delete cascade;(2)Set null例子如下：alter table WDZ2add constraint XXX foreign key (WDZ1ID)references WDZ1 (WDZ1ID) on delete set null;(3)No action注意，这是oracle外键使用时候的默认选项。 例子如下：alter table WDZ2add constraint XXX foreign key (WDZ1ID)references WDZ1 (WDZ1ID);以前出现死锁主要是我们认为，在 1。4。会对数据表wdz2进行施加行级锁，但是从表(wdz2)的外健是No action，删除主表(wdz1)不会去访问从表，更不会去锁定 wdz2表的记录或者对整个数据从表(wdz2)施加表级锁。事实上 oralce的No action 选项的字面意思欺骗了我们，oracle在删除主表的时候会去寻找所有以主表的主键作为外键的数据表，然后看去看从表是否有该外键的索引，如果没有则会对整个从表施加表级锁，然后对从表进行全表扫描。当然如果从表存在外键的索引，会去访问对应的索引，而不会对从表本身进行加锁。3。解决办法为从表的外键建立索引，这也是在使用外键的推荐方式。这样就能避免上面出现的问题了。如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁。预备知识：DDL(DATABASE DEFINITION LANGUAGE)：数据库定义语言，如create table、drop table.DML(DATABASE MODIFICATION LANGUAGE):数据库修改语言，如insert、delete、update.参考资料：Oracle8 Administrators Guide, Release 8.0Oracle8 Tuning, Release 8.0ORACLE锁具体分为以下几类：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锁的一些总结，不足之处还望大家海涵，同时也希望大家多提出自己对ORACLE锁的一些看法。tonnyluo文章选项： 正如上面的实验，互锁或死锁时，ORACLE已经解除了这种锁，所以只要等待某一占有资源的进程打开锁（通过ROLLBACK/COMMIT)即可。对于非正常终止进程占有的锁，把握以前在内部网站上法的一片贴子转贴在下面：锁等待的诊断及排除=在ORACLE中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。 但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现死机，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。 这时，我们需要迅速地诊断出锁住资源的用户并解决其锁定。 1. 诊断系统中的锁为了找出系统中那些用户锁住资源以及那些用户在等待相应的资源，可使用以下语句(其中的/*+ NO_MERGE(.) */千万不可省略, 否则会很慢)：- looklock.sql- use the NO_MERGE hints can speed up the queryselect /*+ 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 ewhere 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 CIQDULMACER 12 966 245 131089select * from c_trade_mode for updateWait CIQUSR CIQDULMACER 10 735 111 131089update c_trade_mode set x_name = zzz where x_code=5Wait CIQUSR CIQDULMACER 15 106 1094 131089select * 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连接异常终止时，因为是异常终止，终止信号并没有通过