ORA-00060: Deadlock detected

最新推荐文章于 2023-02-25 00:40:21 发布

bigclouder

最新推荐文章于 2023-02-25 00:40:21 发布

阅读量979

点赞数

分类专栏： oracle 报错

本文链接：https://blog.csdn.net/kiwi_kid/article/details/25288365

版权

oracle 报错专栏收录该内容

39 篇文章 1 订阅

订阅专栏

今天早上，数据库报

ORA-00060: Deadlock detected. More info in file /app/oracle/diag/rdbms/orcl5/orcl/trace/orcl_ora_48653.trc.

我们查看这个trace文件，发现如下的内容：

*** 2014-05-08 08:30:04.164
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
TX-0009000d-0020148f       107     279     X             96       9           X
TX-0008001d-001eb8d7        96       9     X            107     279           X

session 279: DID 0001-006B-0003315A session 9: DID 0001-0060-00107030
session 9: DID 0001-0060-00107030 session 279: DID 0001-006B-0003315A

Rows waited on:
Session 279: obj - rowid = 000130AD - AAATCtAAGAALi0XAAD
(dictionary objn - 77997, file - 6, block - 3026199, slot - 3)
Session 9: obj - rowid = 000130AD - AAATCtAAGAALklFAAI
(dictionary objn - 77997, file - 6, block - 3033413, slot - 8)

----- Information for the OTHER waiting sessions -----
Session 9:
sid: 9 ser: 15474 audsid: 1316993223 user: 88/SINITEKKM flags: 0x45
pid: 96 O/S info: user: oracle, term: UNKNOWN, ospid: 1090
    image: oracle@linuxdb20
client details:
    O/S info: user: Administrator, term: unknown, ospid: 1234
    machine: WIN-LOVOS6CD0IQ program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
current SQL:
UPDATE V_RSCHDOC_SEARCH SET TITLE = :B12 , INPUTID = :B11 , ORIGINALAUTHOR = :B10 , KEYWORD = :B9 , STATUS = :B8 , SECRET = :B7 , IMPORTANCE = :B6 , ATTACHMENTFLAG = :B5 , COMMENTFLAG = :B4 , DOCTYPEID = :B3 , DOCTYPENAME = :B15 , UPDATETIMESTAMP= :B2 , REMOVETAG = :B1 , SYNTIMESTAMP = :B14 WHERE OBJID = :B13

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=cwpr701k6fkwq) -----
UPDATE V_RSCHDOC_SEARCH SET WRITETIME = :B13 , SUBMITTIME = :B12 , APPROVETIME = :B11 , ARCHIVETIME = :B10 , SECONDTITLE = :B9 , ORIGINALTITLE = :B8 , STKCODE = :B7 , STKNAME = :B21 , INDUSTRYCODE = :B6 , INDUSTRYNAME = :B20 , INVESTRANK = :B19 , INDUSTRYRANK = :B18 , APPROVESTATUS = :B5 , BROKERID = :B4 , BROKERNAME = :B17 , SYNTIMESTAMP = :B16 , INVESTRANKORIGIN = :B3 , SUMMARY = :B2 , TARGETPRICE = :B1 , MKTCODE = :B15 WHERE OBJID = :B14

我们可以看到这个是有触发器引发的死锁

从这个sql中，我们可以了解到更多的关于这个deadlock的细节

关于死锁，一般oracle会自动进行处理，会取消第一事务的最后一步操作，一边让另外的事务可以继续操作

当然，我们可以用一下的sql手动查找，然后杀掉回话，不过这样可能会造成大事务的回滚，非万不得己不要用

select sid,serial#,username from v$session where sid in (select blocking_session from v$session);
然后kill session：
SYS@PROD> alter system kill session '113,30';