oracle数据库死锁原因及分析

定义: 当两个用户希望持有对方的资源时就会发生死锁.

即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.

例子:

1:用户1对A表进行Update,没有提交。

2:用户2对B表进行Update,没有提交。

此时双反不存在资源共享的问题。

3:如果用户2此时对A表作update,则会发生阻塞,需要等到用户一的事物结束。

4:如果此时用户1又对B表作update,则产生死锁。此时Oracle会选择其中一个用户进行会滚,使另一个用户继续执行操作。

起因:

Oracle的死锁问题实际上很少见,如果发生,基本上都是不正确的程序设计造成的,经过调整后,基本上都会避免死锁的发生。

在Oracle系统中能自动发现死锁,并选择代价最小的,即完成工作量最少的事务予以撤消,释放该事务所拥有的全部锁,记其它的事务继续工作下去。

从系统性能上考虑,应该尽可能减少资源竞争,增大吞吐量,因此用户在给并发操作加锁时,应注意以下几点:

1、对于UPDATE和DELETE操作,应只锁要做改动的行,在完成修改后立即提交。

2、当多个事务正利用共享更新的方式进行更新,则不要使用共享封锁,而应采用共享更新锁,这样其它用户就能使用行级锁,以增加并行性。

3、尽可能将对一个表的操作的并发事务施加共享更新锁,从而可提高并行性。

4、在应用负荷较高的期间,不宜对基础数据结构(表、索引、簇和视图)进行修改

如果死锁不能自动释放,就需要我们手工的kill session。 步骤如下:

1. 查看有无死锁对象,如有kill session

/* Formatted on 2010/8/18 9:51:59 (QP5 v5.115.810.9015) */

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"

FROM v$session

WHERE sid IN (SELECT sid

FROM v$lock

WHERE block = 1);

如果有,会返回类似与如下的信息:

alter system kill session '132,731';

alter system kill session '275,15205';

alter system kill session '308,206';

alter system kill session '407,3510';

kill session:

执行alter system kill session '391,48398'(sid为391);

注意: 应当注意对于sid在100以下的应当谨慎,可能该进程对应某个application,如对应某个事务,可以kill.

2. 查看导致死锁的SQL

/* Formatted on 2010/8/18 0:06:11 (QP5 v5.115.810.9015) */

SELECT s.sid, q.sql_text

FROM v$sqltext q, v$session s

WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的

ORDER BY piece;

返回:

SID SQL_TEXT

---------- ----------------------------------------------------------------

77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED

77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON

77 E=9 WHERE PROFILE_USER.ID=:34

3 rows selected.

3. 查看谁锁了谁

/* Formatted on 2010/8/18 0:07:49 (QP5 v5.115.810.9015) */

SELECT s1.username

|| '@'

|| s1.machine

|| ' ( SID='

|| s1.sid

|| ' ) is blocking '

|| s2.username

|| '@'

|| s2.machine

|| ' ( SID='

|| s2.sid

|| ' ) '

AS blocking_status

FROM v$lock l1,

v$session s1,

v$lock l2,

v$session s2

WHERE s1.sid = l1.sid

AND s2.sid = l2.sid

AND l1.BLOCK = 1

AND l2.request > 0

AND l1.id1 = l2.id1

AND l2.id2 = l2.id2;

或者

/* Formatted on 2010/8/18 0:03:46 (QP5 v5.115.810.9015) */

SELECT /*+ rule */

LPAD (' ', DECODE (l.xidusn, 0, 3, 0))

|| l.oracle_username

User_name,

o.owner,

o.object_name,

o.object_type,

s.sid,

s.serial#

FROM v$locked_object l, dba_objects o, v$session s

WHERE l.object_id = o.object_id AND l.session_id = s.sid

ORDER BY o.object_id, xidusn DESC

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180305A0Z0DK00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券