并发访问oracle数据库的数据死锁分析和解决措施,解决Oracle数据库死锁

【IT168 技术文档】

介绍

本文我们尝试总结在多个用户并发情况下，如何识别和解决删除操作期间发生的死锁问题，在开始之前，我们先简单描述一下什么是死锁以及什么东西会导致死锁。

死锁

在任何数据库中发生死锁都是不愉快的，即使是在一个特殊的情况下发生也是如此，它们会减小应用程序的接受程度(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;