`

Oracle常见死锁发生的原因以及解决办法

阅读更多

一,删除和更新之间引起的死锁

造成死锁的原因就是多个线程或进程对同一个资源的争抢或相互依赖。这里列举一个对同一个资源的争抢造成死锁的实例。

Oracle 10g, PL/SQL version 9.2

CREATE TABLE testLock(  ID NUMBER, 

test VARCHAR(100) 

COMMIT  

 

INSERT INTO testLock VALUES(1,'test1'); 

INSERT INTO testLock VALUES(2,'test2'); 

COMMIT

SELECT * FROM testLock 

   

  1.         ID TEST  
  2. ---------- ----------------------------------  
  3.          1 test1  
  4.          2 test2  

 

死锁现象的重现:

1)在sql 窗口 执行:SELECT * FROM testLock FOR UPDATE; -- 加行级锁 并对内容进行修改,不要提交

2)另开一个command窗口,执行:delete from testLock WHERE ID=1;

此时发生死锁(注意此时要另开一个窗口,不然会提示:POST THE CHANGE RECORD TO THE DATABASE. 点yes 后强制commit):

3)死锁查看:

  1. SQL>  select s.username,l.object_id, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;</p><p>USERNAME    SESSION_ID  SERIAL#    LOCKWAIT STATUS   MACHINE                 PROGRAM  
  2. ----------  ----------  ---------- -------- -------- ----------------------  ------------  
  3. SYS         146         104                 INACTIVE WORKGROUP\J-THINK       PLSQLDev.exe  
  4. SYS         144         145        20834474 ACTIVE   WORKGROUP\J-THINK       PLSQLDev.exe

字段说明:
Username:死锁语句所用的数据库用户;
SID: session identifier, session 标示符,session 是通信双方从开始通信到通信结束期间的一个上下文。
SERIAL#: sid 会重用,但是同一个sid被重用时,serial#会增加,不会重复。
Lockwait:可以通过这个字段查询出当前正在等待的锁的相关信息。
Status:用来判断session状态。Active:正执行SQL语句。Inactive:等待操作。Killed:被标注为删除。
Machine: 死锁语句所在的机器。
Program: 产生死锁的语句主要来自哪个应用程序。

4)查看引起死锁的语句:

SQL>  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));  

  1.   
  2. SQL_TEXT  
  3. ------------------------------------------------------------  
  1. delete from testLock where  ID = 1  


5)死锁的处理:

SQL> alter system kill session '144,145';  

  1.   
  2. System altered  
  3.   
  4. Executed in 1.061 seconds  


此时在执行delete语句的窗口出现:

SQL> delete from testLock where  ID = 1;  

  1.   
  2. delete from testLock where  ID = 1  
  3.   
  4. ORA-00028: 您的会话己被终止  


再查看一下死锁,会发现已经没有stauts为active的记录了:

SQL>  select s.username, l.session_id,s.serial#, s.lockwait,s.status,s.machine,s.program from v$session s,v$locked_object l where s.sid = l.session_id;  

  1.   
  2. USERNAME                 SESSION_ID SERIAL#  LOCKWAIT STATUS   MACHINE             PROGRAM  
  3. ------------- ---------- ---------- -------- -------- ---------------------------  ----------------  
  1. SYS                      146        104               INACTIVE WORKGROUP\J-THINK   PLSQLDev.exe  
  2.   
  3. Executed in 0.032 seconds  


发生死锁的语句已经被终止。

 

二,在外键上没有加索引引起的死锁

 

客户的10.2.0.4 RAC for AIX环境频繁出现ORA-60死锁问题,导致应用程序无法顺利执行。
经过一系列的诊断,发现最终问题是由于外键上没有建立索引所致,由于程序在主子表上删除数据,缺少索引导致行级锁升级为表级锁,最终导致大量的锁等待和死锁。
下面通过一个例子简单模拟一下问题:
SQL> create table t_p (id number primary key, name varchar2(30));
Table created.
SQL> create table t_f (fid number, f_name varchar2(30), foreign key (fid) references t_p);
Table created.
SQL> insert into t_p values (1, 'a');
1 row created.
SQL> insert into t_f values (1, 'a');
1 row created.
SQL> insert into t_p values (2, 'b');
1 row created.
SQL> insert into t_f values (2, 'c');
1 row created.
SQL> commit;
Commit complete.
SQL> delete t_f where fid = 2;
1 row deleted.
这时在会话2同样对子表进行删除:
SQL2> delete t_f where fid = 1;
1 row deleted.
回到会话1执行主表的删除:
SQL> delete t_p where id = 2;
会话被锁,回到会话2执行主表的删除:
SQL2> delete t_p where id = 1;
会话同样被锁,这时会话1的语句被回滚,出现ORA-60死锁错误:
delete t_p where id = 2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
SQL> rollback;
Rollback complete.
将会话1操作回滚,会话2同样回滚并建立外键列上的索引:
1 row deleted.
SQL2> rollback;
Rollback complete.
SQL2> create index ind_t_f_fid on t_f(fid);
Index created.
重复上面的步骤会话1删除子表记录:
SQL> delete t_f where fid = 2;
1 row deleted.
会话2删除子表记录:
SQL2> delete t_f where fid = 1;
1 row deleted.
会话1删除主表记录:
SQL> delete t_p where id = 2;
1 row deleted.
会话2删除主表记录:
SQL> delete t_p where id = 1;
1 row deleted.
所有的删除操作都可以成功执行,关于两种情况下锁信息的不同这里就不深入分析了,重点就是在外键列上建立索引。
虽然有一些文章提到过,如果满足某些情况,可以不在外键列上建立的索引,但是我的观点一向是,既然创建了外键,就不要在乎再多一个索引,因为一个索引所增加的代价,与缺失这个索引所带来的问题相比,是微不足道的。


【补充】Oracle 10g和Oracle 9i trc日志内容的差别
最主要的差别是在Oracle 10g中提示了等待资源的两条sql语句,在Oracle 9i中,只显示检测到死锁的sql语句
Oracle 10g 10.2.0.3.0:

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-0000dd55-00000000        16     146    SX   SSX       17     148    SX   SSX
TM-0000dd55-00000000        17     148    SX   SSX       16     146    SX   SSX
session 146: DID 0001-0010-00000008	session 148: DID 0001-0011-00000006
session 148: DID 0001-0011-00000006	session 146: DID 0001-0010-00000008
Rows waited on:
Session 148: no row
Session 146: no row
Information on the OTHER waiting sessions:
Session 148:
  pid=17 serial=39 audsid=540046 user: 54/SCOTT
  O/S info: user: SKYHOME\sky, term: SKYHOME, ospid: 3028:7000, machine: WORKGROUP\SKYHOME
            program: plsqldev.exe
  application name: PL/SQL Developer, hash value=1190136663
  action name: Command Window - New, hash value=254318129
  Current SQL Statement:
  
delete t_p where id = 1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
delete t_p where id = 2


Oracle 9i 9.2.0.7.0:

DEADLOCK DETECTED
Current SQL statement for this session:
delete t_p where id = 2
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-0000260e-00000000        21      51    SX   SSX       23      20    SX   SSX
TM-0000260e-00000000        23      20    SX   SSX       21      51    SX   SSX
session 51: DID 0001-0015-0000043D	session 20: DID 0001-0017-00000397
session 20: DID 0001-0017-00000397	session 51: DID 0001-0015-0000043D
Rows waited on:
Session 20: no row
Session 51: no row
Information on the OTHER waiting sessions:
Session 20:
  pid=23 serial=53179 audsid=197296 user: 87/scott
  O/S info: user: sky, term: SKYHOME, ospid: 5540:4984, machine: WORKGROUP\SKYHOME
            program: plsqldev.exe
  client info: 127.0.0.1
  application name: PL/SQL Developer, hash value=1190136663
  action name: Command Window - New, hash value=254318129
  Current SQL Statement:
  
delete t_p where id = 1
End of information on OTHER waiting sessions.

 

三,两个表之前不同顺序之间的相互更新操作引起的死锁

 

Oracle中的死锁:
 

 
   注:4个update语句的执行顺序按图中位置自上而下
图中左边会话中断(此时不回滚也不提交,等待用户决定),右边会话阻塞,等待左边会话释放a表上的锁。如图:
 

 
 
死锁解决方法:



 


 修改应用!参考以下方法。 1、将死锁减至最少 虽然不能完全避免死锁,但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销,因为只有很少的事务: 
 回滚,而回滚会取消事务执行的所有工作。  由于死锁时回滚而由应用程序重新提交。  下列方法有助于最大限度地降低死锁: 
 按同一顺序访问对象。  避免事务中的用户交互。
 保持事务简短并在一个批处理中。  使用低隔离级别。  使用绑定连接。  2、按同一顺序访问对象
如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。例如,如果两个并发事务获得 Supplier 表上的锁,然后获得 Part 表上的锁,则在其中一个事务完成之前,另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后,第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。
 

 
3、避免事务中的用户交互 避免编写包含用户交互的事务,因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度,例如答复应用程序请求参数的提示。例如,如果事务正在等待用户输入,而用户去吃午餐了或者甚至回家过周末了,则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况,访问同一资源的其它事务也会被阻塞,等待该事务完成。 4、保持事务简短并在一个批处理中
在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。 保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。

 

原文摘自:

http://liwenshui322.iteye.com/blog/722712

http://biancheng.dnbcw.info/oracle/385142.html

http://wenku.baidu.com/link?url=MhEcvSfC686wE-GGcSVnOf02R_6y6nsiq9pMOE2sHTlIXSAIIk89mlVm8eTBDaA8IxOAY_F_1e2U3s7jYhGlpbT5MOBwxlCyFtdNjXC0UyW

  • 大小: 76.9 KB
  • 大小: 48.5 KB
  • 大小: 57 KB
分享到:
评论

相关推荐

    oracle死锁原因解决办法

    ### Oracle死锁原因及解决办法 #### 一、Oracle死锁概述 在Oracle数据库系统中,死锁是一种常见的并发问题,它会导致多个事务之间互相等待对方释放资源而无法继续执行,最终导致整个系统的运行效率降低甚至停滞。...

    ORACLE表死锁的解决方法亲测有效!

    本文将深入探讨ORACLE表死锁的成因、检测与解决方法,基于实际测试经验分享有效的解决方案。 ### ORACLE表死锁的成因 死锁通常发生在多个事务同时对同一资源进行互斥访问的情况下。具体而言,当一个事务请求锁定一...

    oracle死锁故障分析和诊断解决

    ### Oracle死锁故障分析与诊断解决 在Oracle数据库管理中,...总之,Oracle死锁虽然是一种常见问题,但通过合理的诊断和解决方案,以及有效的预防措施,可以大大降低其对数据库性能的影响,确保数据库系统的稳定运行。

    oracle查找死锁

    ### Oracle查找及解决死锁的方法 在Oracle数据库的日常管理和维护过程中,死锁是一个常见的问题。当两个或多个会话互相等待对方释放资源时就会发生死锁,这会导致相关事务无法继续执行,甚至可能会影响到整个数据库...

    Oracle数据库死锁查询语句

    总的来说,理解Oracle数据库死锁的概念、检测方法以及预防策略是数据库管理的重要部分。通过合理的设计、编程实践和定期的性能监控,可以有效地防止和解决死锁问题,确保数据库系统的稳定运行。

    并发访问ORACLE数据库的数据死锁分析和解决措施.pdf

    数据死锁是一个关系型数据库中常见的问题,本文对数据死锁的概念、原因和解决措施进行了详细介绍。通过合理设计事务的并发性、锁机制和事务的执行时间,可以避免数据死锁问题,提高数据库的性能和可靠性。

    oracle如何解除死锁

    接下来通过一个简单的例子来说明死锁是如何产生的以及如何解决。 假设有一个表`t2`,其结构如下: | ID | |----| | 1 | | 2 | | 3 | - **Session 1**: ```sql UPDATE t2 SET id = '11' WHERE id = '1'; ``` ...

    oracle中关于死锁的处理

    在Oracle数据库中,死锁是一种常见但必须妥善处理的问题。当两个或多个事务互相等待对方释放资源时就会发生死锁。这种情况下,没有一个事务能够继续执行,直到系统采取干预措施解决死锁。 #### 处理方法 1. **手动...

    oracle解决死锁![参考].pdf

    以下是一些解决Oracle死锁的方法: 首先,定位死锁发生的进程是非常重要的。通过查询`V$DB_OBJECT_CACHE`视图可以找出被锁住的对象,然后通过`V$ACCESS`视图进一步获取被锁定的进程信息,如所属用户、过程名。接着...

    ORA-00060: 等待资源时检测到死锁--oracle 数据库表死锁异常

    为了解析这个问题,我们需要深入理解Oracle数据库的锁定机制、死锁的原因以及如何诊断和解决死锁。 首先,Oracle数据库使用多粒度锁定(Multigranularity Locking,MGL)机制,提供行级、块级和表级的锁定。当事务...

    教您如何检查oracle死锁

    #### 四、解决Oracle死锁的方法 一旦定位到死锁的具体会话和SQL语句,接下来就需要采取措施解决这个问题。常见的解决方法包括: 1. **手动干预**:根据死锁报告中的信息,手动回滚其中一个事务,以解除死锁。 2. *...

    oracle 死锁时候 ,杀进程方法

    在Oracle数据库管理中,死锁是一个常见的问题,它通常发生在两个或多个事务互相等待对方释放资源的情况。当这种情况发生时,可能会导致应用程序响应变慢甚至完全停止运行。因此,了解如何有效地解决Oracle死锁问题至...

    SQL死锁,删除失败解决办法

    SQL死锁是一种常见的数据库性能问题,了解其原因及处理方法对于DBA和开发人员来说非常重要。通过上述介绍的方法和技术,可以有效地检测和解决死锁问题,从而保证系统的稳定性和高效性。此外,还可以通过调整数据库...

    分析+Oracle+死锁的检测查询及处理方法

    Oracle数据库中的死锁是数据库操作中常见的问题,它发生在两个或多个事务相互等待对方释放资源,从而导致事务无法继续执行的情况。死锁不仅影响数据库性能,还可能导致数据一致性问题。本篇文章将详细介绍如何检测和...

    查看 oracle 死锁程序

    死锁是数据库环境中常见的并发问题之一,它发生在两个或多个事务互相等待对方释放资源的情况下,最终导致所有涉及事务都无法继续执行。通过有效的监控工具,管理员可以及时发现并解决这些问题,避免对业务造成不必要...

    Oracle对于死锁的处理方法

    此外,合理安排事务的执行顺序,减少事务的复杂性和运行时间,也可以有效降低死锁发生的风险。如果问题经常发生,可能需要考虑是否需要调整数据库的配置参数,比如锁等待超时时间等。总之,死锁的处理需要结合实际...

    查出、删除死锁对象

    在数据库系统中,特别是在Oracle这样的大型关系型数据库管理系统中,死锁是常见的问题之一。当两个或多个事务互相等待对方释放资源时,就会发生死锁。这种情况下,所有涉及的事务都将处于无限等待状态,从而导致应用...

    oracle查询kill死锁方法.txt

    ### Oracle 查询及解除死锁的方法 #### 一、理解Oracle中的死锁现象 在Oracle数据库中,当两个或...总之,在Oracle数据库中遇到死锁问题是比较常见的,但只要掌握了正确的查询和处理方法,就能够及时有效地解决问题。

    简单说明Oracle数据库中对死锁的查询及解决方法

    在Oracle数据库中,死锁是一种常见的并发控制问题,它发生在两个或多个事务互相等待对方释放资源,导致它们都无法继续执行。死锁的原理在于,当两个事务A和B分别持有对方需要的资源时,如果A等待B释放资源而B也在...

Global site tag (gtag.js) - Google Analytics