bug之旅1.2：java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

最新推荐文章于 2024-09-09 20:17:18 发布

suncunlu

最新推荐文章于 2024-09-09 20:17:18 发布

阅读量1.3k

点赞数

分类专栏： bug系列文章标签： java

本文链接：https://blog.csdn.net/suncunlu/article/details/106859667

版权

本文详细记录了一次在Java应用程序中遇到的ORA-00060死锁问题的排查过程。从错误日志开始，分析了Oracle数据库的死锁日志，定位到引发死锁的SQL语句，并进一步在代码层面进行分析，最终提出了解决死锁问题的排序策略。通过这个案例，展示了如何高效地解决数据库死锁问题。

摘要由CSDN通过智能技术生成

1、错误日志------------------------------------------------------------------

2020-XX-XX 15:32:01.808->
功能号：UPDATE StkInfo SET ExchIDEx=’’,stkName=‘19杭实01’,ShortPrefix=’’,…afterLowerLimit=0 WHERE exchId=‘0’ AND stkId=‘155400’
java.sql.SQLException: ORA-00060: 等待资源时检测到死锁
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1814)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
at com.XXXXXXX
2020-XX-XX 15:32:02.033->java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

2、oracle死锁日志------------------------------------------------------------------

2.1、获取db死锁日志，

/u01/oracle/diag/rdbms/pbdb/XXX/alert/log.xml
->

ORA-00060: Deadlock detected. More info in file /u01/oracle/diag/rdbms/pbdb/PBDB_VER/trace/PBDB_VER_ora_14465.trc.

->
/u01/oracle/diag/rdbms/pbdb/PBDB_VER/trace/PBDB_VER_ora_14465.trc

2.2、打开错误日志。------------------------------------------------------------------

->
1、
*** 2020-XX-XX 15:35:15.720
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-000b0008-00030a61 322 408 X 321 243 X
TX-000a001e-0003e86a 321 243 X 322 408 X

session 408: DID 0001-0142-0000008E session 243: DID 0001-0141-00000074
session 243: DID 0001-0141-00000074 session 408: DID 0001-0142-0000008E

Rows waited on:
Session 408: obj - rowid = 000144B5 - AAAUS1AAQAAAIosAAI
(dictionary objn - 83125, file - 16, block - 35372, slot - 8)
Session 243: obj - rowid = 000144B5 - AAAUS1AAXAAAQK9AAV
(dictionary objn - 83125, file - 23, block - 66237, slot - 21)

----- Information for the OTHER waiting sessions -----
Session 243:
sid: 243 ser: 4235 audsid: 1152811