今天早上,数据库报
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';