1 Reply Latest reply: Mar 28, 2013 10:07 PM by 816366 RSS

    Oracle死锁问题

    964110
      Oracle 10.2.0.1.0 64位 ,操作系统 AIX 6.1
      近期在查看alert日志时发现如下信息
      Mon Nov 5 16:02:40 2012
      ORA-00060: Deadlock detected. More info in file /oradata/admin/$ORACLE_SID/udump/$ORACLE_SID_ora_6554104.trc.
      Mon Nov 5 16:06:52 2012
      ORA-00060: Deadlock detected. More info in file /oradata/admin/$ORACLE_SID/udump/$ORACLE_SID_ora_9961902.trc.
      Mon Nov 5 16:51:21 2012
      提示检查到死锁,然后查看trace文件

      more /oradata/admin/$ORACLE_SID/udump/$ORACLE_SID_ora_6554104.trc

      前面无关内容省略

      *** 2012-11-05 16:02:40.639
      *** SERVICE NAME:(SYS$USERS) 2012-11-05 16:02:40.638
      *** SESSION ID:(154.37034) 2012-11-05 16:02:40.638
      DEADLOCK DETECTED
      [Transaction Deadlock]
      Current SQL statement for this session:
      UPDATE "RZ_DLOGIN" SET "D_DATA" = :1 WHERE "G_NAME" = :2 AND "D_CODE" = :3 AND "CLASS_ID" = :4
      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
      TX-00080028-0000d238 68 154 X 81 97 X
      TX-0005001d-0000ac81 81 97 X 68 154 X
      session 154: DID 0001-0044-00000E1E session 97: DID 0001-0051-00000BDA
      session 97: DID 0001-0051-00000BDA session 154: DID 0001-0044-00000E1E
      Rows waited on:
      Session 97: obj - rowid = 0000D950 - AAANlQAAFAAA/ilABD
      (dictionary objn - 55632, file - 5, block - 260261, slot - 67)
      Session 154: obj - rowid = 0000D950 - AAANlQAAFAAA/ilAA7
      (dictionary objn - 55632, file - 5, block - 260261, slot - 59)
      Information on the OTHER waiting sessions:
      Session 97:
      pid=81 serial=21773 audsid=600338 user: 55/DB
      O/S info: user: Administrator, term: name1, ospid: 1408:1296, machine: factory\name1
      program: name.exe
      application name: name.exe, hash value=0
      Current SQL Statement:
      UPDATE "RZ_DLOGIN" SET "D_DATA" = :1 WHERE "G_NAME" = :2 AND "D_CODE" = :3 AND "CLASS_ID" = :4
      End of information on OTHER waiting sessions.

      经过与开发人员沟通,已确认 UPDATE "RZ_DLOGIN" SET "D_DATA" = :1 WHERE "G_NAME" = :2 AND "D_CODE" = :3 AND "CLASS_ID" = :4 这一语句对应的前端应用A,与程序使用者沟通了解到前端应用A确认运行较慢,一般要等待一段时间才能完成,有时也会遇到等待无反应的情况,此时关闭应用A程序,重新运行就会正常。同时将上述日志中发现的问题反映给开发人员,开发人员也证实此处程序需要优化。
      现在的问题是: 既然Oracle日志中已经提示检查到死锁,为何前端应用A只是等待了一段时间(这段等待时间用户还能够接受)就能够继续使用了,此时用下列语句查询数据库中是否存在死锁
      select username,lockwait,status,machine,program from v$session where sid in
      (select session_id from v$locked_object)

      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检测到死锁后,没有任何人处理,死锁又自己消失了,难道是Oracle后台进程可以智能处理这些死锁?
      另外,在百度查到关于死锁的处理方法,有这么一段话:
      一般情况下,只要将产生死锁的语句提交就可以了,但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
      这个说法似乎也与前端应用A的用户遭遇的情况差不多:有时候如果等待无反应,关闭A程序,然后重新运行就正常了。
      请问百度里的这个说法是不是正确的?是不是以后遇到Oracle数据库死锁都可以无视它,因为Oracle可以自己解决?

      日志已发到刘大gmail邮箱,请指点

      帖子经 user10404229编辑过