解决Oracle数据库死锁

解决Oracle数据库死锁
　　介绍
　　本文我们尝试总结在多个用户并发情况下，如何识别和解决删除操作期间发生的死锁问题，在开始之前，我们先简单描述一下什么是死锁以及什么东西会导致死锁。
　　死锁
　　在任何数据库中发生死锁都是不愉快的，即使是在一个特殊的情况下发生也是如此，它们会减小应用程序的接受程度(ACCEPTANCE)，因此避免并正确解释死锁是非常重要的。
　　当两个或更多用户相互等待锁定的数据时就会发生死锁，发生死锁时，这些用户被卡住不能继续处理业务，Oracle自动检测死锁并解决它们(通过回滚一个包含在死锁中的语句实现)，释放掉该语句锁住的数据，回滚的会话将会遇到Oracle错误“ORA-00060：等待资源时检测到死锁”。
　　是什么导致了死锁?
　　明确地锁定表是为了保证读/写一致性，未建立索引的外键约束，在相同顺序下表不会锁住，没有为数据段分配足够的存储参数(主要是指INITTRANS，MAXTRANS和PCTFREE参数)很容易引发突发锁和死锁，原因是多种多样的，需要重新逐步审查。
　　识别死锁
　　当Oracle数据库检测到死锁时(Oracle错误消息：ORA-00060)，相应的消息就写入到警告日志文件中(alert.log)，另外还会在USER_DUMP_DEST目录下创建一个跟踪文件，分析警告日志文件和跟踪文件是非常耗时的。
　　下面是一个警告日志文件示例：
　　Mon Aug 07 09:14:42 2007
　　ORA-000060: Deadlock detected. More info in file
　　e:\oracle\admin\GEDEON\udump\ORA01784.TRC.
　　下面是从跟踪文件中节选出来的片段，从其中我们可以看出是哪个语句创造了死锁，相关的语句和被锁定的资源已经标记为粗体。
　　/users/ora00/log/odn_ora_1097872.trc
　　Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
　　With the Partitioning, OLAP and Oracle Data Mining options
　　JServer Release 9.2.0.8.0 - Production
　　ORACLE_HOME = /soft/ora920
　　System name:AIX
　　Node name:beaid8
　　Release:2
　　Version:5
　　Machine:00C95B0E4C00
　　Instance name: ODN
　　Redo thread mounted by this instance: 1
　　Oracle process number: 17
　　Unix process pid: 1097872, image: oracle@beaid8 (TNS V1-V3)
　　*** 2007-06-04 14:41:04.080
　　*** SESSION ID:(10.6351) 2007-06-04 14:41:04.079
　　DEADLOCK DETECTED ( ORA-00060 )
　　The following deadlock is not an ORACLE error. It is a
　　deadlock due to user error in the design of an application
　　or from issuing incorrect ad-hoc SQL. The following
　　information may aid in determining the deadlock:
　　Deadlock graph:
　　---------Blocker(s)-------- ---------Waiter(s)---------
　　Resource Name process session holds waits process session holds waits
　　TM-00001720-00000000 17 10 SX 16 18 SX SSX
　　TM-0000173a-00000000 16 18 SX 17 10 SX SSX
　　session 10: DID 0001-0011-00000002session 18: DID 0001-0010-00000022
　　session 18: DID 0001-0010-00000022session 10: DID 0001-0011-00000002
　　Rows waited on:
　　Session 18: obj - rowid = 00001727 - AAABcnAAJAAAAAAAAA
　　(dictionary objn - 5927, file - 9, block - 0, slot - 0)
　　Session 10: obj - rowid = 00001727 - AAABcnAAJAAAAAAAAA
　　(dictionary objn - 5927, file - 9, block - 0, slot - 0)
　　Information on the OTHER waiting sessions:
　　Session 18:
　　pid=16 serial=2370 audsid=18387 user: 21/ODN
　　O/S info: user: mwpodn00, term: unknown, ospid: , machine: beaida
　　program: JDBC Thin Client
　　application name: JDBC Thin Client, hash value=0
　　Current SQL Statement:
　　DELETE FROM ODNQTEX WHERE EX_ID = :B1
　　End of information on OTHER waiting sessions.
　　Current SQL statement for this session:
　　DELETE FROM ODNQTFN WHERE FN_ID_EXIGENCE_EX = :B1
　　----- PL/SQL Call Stack -----
　　object line object
　　handle number name
　　7000000135f7fd8 34 procedure ODN.ODNQPDR
　　7000000135f89f0 16 procedure ODN.ODNQPZB
　　我们可以使用企业管理器来决定保留所有的锁还是释放掉它们，为了便于说明，我们打开2个sqlplus实例会话(在此期间同时发生了死锁)来一起调式，当每个语句执行完毕后，我们看到锁仍然保留下来了，它可以帮助我们识别出是哪个资源引起的死锁。
　　下面列出了可以帮助我们监视锁的视图：

　　可以通过查询V$LOCK字典视图来确定锁，如：
       
         select * from v$lock ;
　　下面的SQL查询可以用于确定锁住数据库对象的锁：
　　select
　　c.owner,
　　c.object_name,
　　c.object_type,
　　b.sid,
　　b.serial#,
　　b.status,
　　b.osuser,
　　b.machine
　　from
　　v$locked_object a ,
　　v$session b,
　　dba_objects c
　　where
　　b.sid = a.session_id
　　and
　　a.object_id = c.object_id;
　　解决死锁
　　安装顺序执行下面的修改，避免一致性访问期间的死锁问题：
　　设置事务一致性：我们需要确定一个隔离水平，在存储过程中可以使用“READ COMMITTED”或“SERIALIZABLE”，我们需要考虑两件事情：
　　 设置事务可以在任何时间提交
　　 相关表中行的读取顺序
　　Oracle数据库隔离模式通过行级锁和Oracle数据库多版本并发控制系统提供高级一致性和并发性(和高性能)，READ COMMITTED模式可以提供更多的并发性，因为没有重复读，SERIALIZABLE隔离水平提供了更好的一致性，通过保护非重复读实现，在一个读写事务执行不止一次查询时这很重要，然而，SERIALIZABLE模式需要应用程序检查“不能连续访问”的错误，这样就可以在有许多访问相同数据的一致性事务的环境中大大减少吞吐量。
　　在我们的例子中，我们需要使用READ COMMITTED隔离水平，原因如下：
　　我们需要获取由另一个事务提交的查询返回的行，不仅仅是获取刚开始的事务返回的行。
　　如果我们将隔离水平设为SERIALIZABLE，将会获得一个“事务无法按顺序访问”的错误，因为我们想要修改的数据已经被另一个事务修改了。
　　在存储过程中我们放入下面的语句：
　　SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
　　明确锁定数据：Oracle数据库总是执行必要的锁确保数据的并发性、完整性和语句级读一致性，但在我们的例子中，我们需要独占访问资源，为了处理它的语句，事务独占访问资源，不要等待其他事务完成。
　　我将会用一个例子来解释，在我们的案例中，需要删除一个业务对象System，为了删除这个对象，首先我们需要删除System子表中的所有数据，现在进入其中一个子表，我们在子表上创建一个关于删除的触发器，这个触发器更新主表System的数据并锁住它，接下来进入删除操作，当我们想删除System表中的数据时，就会出现死锁，因为它已经被前面子表上的触发器给锁住了，为了避免出现这种情况，在删除操作开始之前，我们提供一个行独占锁锁住System表来解决这个问题。命令如下：
　　LOCK TABLE ODNQTSY IN ROW EXCLUSIVE MODE;
　　并行索引和查询处理：Oracle数据库使用索引增强SQL查询的性能，这有助于我们执行DML操作，如插入、更新和删除，做这些动作的时间将会极具减少了。
　　并行索引将会让优化器思考执行并行查询时使用索引，这是避免死锁的一个方法，当索引重建后，Oracle将会允许多个DML操作发生在同一个索引上，在索引上启用并行操作的语法如下：
　　ALTER INDEX PARALLEL;
　　使用并行查询选项的最大好处是直接路径读取，因此需要的latch就更少了，同样，并行执行大大减少了数据密集型业务的响应时间。
　　并行执行选项可以通过修改INIT.ORA文件中的PARALLEL_AUTOMATIC_TUNING参数(设为TRUE)实现数据库级别的启用。
　　parallel_automatic_tuning=TRUE
　　外键上的索引：外键上如果没有建立索引会引发两个问题，第一个是如果你更新父记录主键或删除父记录，子表的外键没有索引时，会引发表级锁;第二个问题是性能。
　　如果你试图删除父表行，或更新父/子关联中父表行的键值，而子表的外键上没有索引时，Oracle将会尝试在子表上获得一个共享行级独占锁，接下来如果有其它会话要修改子表，它将不得不等待在它前面的SRX锁(共享行级独占锁)，这样就形成了一个死锁状态。
　　下面的脚本可以帮助我们识别没有索引的外键(FK)约束，从脚本执行的输出中，我们可以确定在外键上创建索引将可以帮助我们改善性能，并且可以避免死锁。
　　column columns format a20 word_wrapped
　　column table_name format a30 word_wrapped
　　select decode( b.table_name, NULL, '****', 'ok' ) Status,
　　a.table_name, a.columns, b.columns
　　from
　　( select substr(a.table_name,1,30) table_name,
　　substr(a.constraint_name,1,30) constraint_name,
　　max(decode(position, 1, substr(column_name,1,30),NULL)) ||
　　max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
　　from user_cons_columns a, user_constraints b
　　where a.constraint_name = b.constraint_name
　　and b.constraint_type = 'R'
　　group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
　　( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
　　max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
　　max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
　　from user_ind_columns
　　group by substr(table_name,1,30), substr(index_name,1,30) ) b
　　where a.table_name = b.table_name (+)
　　and b.columns (+) like a.columns || '%'
　　外键的级联删除：在我们的案例中，已经使用“级联删除选项(On Delete Cascade)”创建了一些外键约束，这样会引发死锁问题，原因是在我们的删除事务中，我们明确地删除子表中的数据，然后再删除主表中的数据，因此在子表上就已经存在一个锁了，在删除主表之前，我们想再删除子表一次，因此导致的死锁。
　　为了解决这个问题，我们移除了“级联删除选项(On Delete Cascade)”，这样就修复了死锁问题。











下面补充一些必备的基础知识！

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首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。




关于数据库死锁的检查方法

一、         数据库死锁的现象
程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。
二、         死锁的原理
当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提
交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，
此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。
三、         死锁的定位方法
通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句。
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

四、         死锁的解决方法
     一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

　　1）查找死锁的进程：

sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

　　2）kill掉这个死锁的进程：

　　alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

　　3）如果还不能解决：

select pro.spid from v$session ses,
v$process pro where ses.sid=XX and
ses.paddr=pro.addr;

　　其中sid用死锁的sid替换:

exit
ps -ef|grep spid

　　其中spid是这个进程的进程号，kill掉这个Oracle进程