查明ORA-60死锁

最新推荐文章于 2024-08-13 10:09:13 发布

cuishandun3253

最新推荐文章于 2024-08-13 10:09:13 发布

阅读量116

点赞数

文章标签：数据库操作系统

背景：2015-02-03 09:10:34 接到手机报警短信，数据库报ORA-60死锁

登陆服务器查看ALERT日志，已产生TRACE文件
$ tail -n 200 alert_sgrd.log
Tue Feb 03 09:08:27 2015
ORA-00060: Deadlock detected. More info in file /oracle/diag/rdbms/sgrd/sgrd/trace/sgrd_ora_19874.trc.

查看TRACE文件：
TRACE文件提供了引起死锁的两个SESSION相关信息（SID,SERIAL#，PID,SQL_ID等,引起死锁的sql语句等）

[oracle@Sps154 trace]$ cat /oracle/diag/rdbms/sgrd/sgrd/trace/sgrd_ora_19874.trc | more
Trace file /oracle/diag/rdbms/sgrd/sgrd/trace/sgrd_ora_19874.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2
System name:    Linux
Node name:      xxxxxxx
Release:        xxxxxxx
Version:        #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine:        x86_64
Instance name: sgrd
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 19874, image: oracle@Sps154

*** 2015-02-03 09:08:26.841
*** SESSION ID:(459.15620) 2015-02-03 09:08:26.841
*** CLIENT ID:() 2015-02-03 09:08:26.841
*** SERVICE NAME:(SYS$USERS) 2015-02-03 09:08:26.841
*** MODULE NAME:(JDBC Thin Client) 2015-02-03 09:08:26.841
*** ACTION NAME:() 2015-02-03 09:08:26.841

*** 2015-02-03 09:08:26.841
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-0001204a-00000000        29     459    SX   SSX       35     111    SX   SSX
TM-0001204a-00000000        35     111    SX   SSX       29     459    SX   SSX

session 459: DID 0001-001D-000B1D0C     session 111: DID 0001-0023-00467C13
session 111: DID 0001-0023-00467C13     session 459: DID 0001-001D-000B1D0C

Rows waited on:
Session 459: no row
Session 111: no row

----- Information for the OTHER waiting sessions -----
Session 111:
sid: 111 ser: 14956 audsid: 25861584 user: 88/88 flags: 0x100045
pid: 35 O/S info: user: oracle, term: UNKNOWN, ospid: 19295
    image: xxxxxxx
client details:
    O/S info: user: xxxxx, term: unknown, ospid: 1234
    machine: xxxxxxx
current SQL:
DELETE FROM T_CYMENT WHERE claim_payment_seq = :1

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

Information for THIS session:

----- Current SQL Statement for this session (sql_id=9m2w9b8t4vs92) -----
DELETE FROM T_CYMENT WHERE claim_payment_seq = :1

=====================================
根据Deadlock graph查看相关SESSIO信息：

                       ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TM-0001204a-00000000        29     459    SX   SSX       35     111    SX   SSX
TM-0001204a-00000000        35     111    SX   SSX       29     459    SX   SSX

查看sql语句代码：
select SQL_FULLTEXT ,
       SQL_ID,
       USERS_OPENING ,
       PLSQL_EXEC_TIME,
       JAVA_EXEC_TIME ,
       ACTION
from v$sql
       where sql_id='9m2w9b8t4vs92';

SQL_FULLTEXT                                                                                    SQL_ID                 USERS_OPENING         PLSQL_EXEC_TIME JAVA_EXEC_TIME ACTION
--------------------------------------------------------------------------------            -------------            ------------- --------------- -------------            - ----------------------------------------------------------------
DELETE FROM T_CYMENT WHERE claim_payment_seq = :1                     9m2w9b8t4vs92             0               0              0
DELETE FROM T_CYMENT WHERE claim_payment_seq = :1                     9m2w9b8t4vs92             1               0              0
DELETE FROM T_CYMENT WHERE claim_payment_seq = :1                     9m2w9b8t4vs92             0               0              0

SQL>

SESSION 459 相关信息：
SQL> select SAMPLE_TIME ,
2              USER_ID ,
3              SESSION_ID,
4              SESSION_SERIAL# ,
5              SQL_EXEC_START,
6              sql_id,EVENT,
7              BLOCKING_SESSION,
8              BLOCKING_SESSION_SERIAL#,
9              BLOCKING_INST_ID,
10              MACHINE
11        from v$active_session_history
12        where SESSION_SERIAL#=15620
13              and session_id=459
14              and sql_id='9m2w9b8t4vs92'
15        order by SQL_EXEC_START;

SAMPLE_TIME                       USER_ID SESSION_ID SESSION_SERIAL# SQL_EXEC_START            SQL_ID        EVENT                                                            BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MACHINE
------------------------------ ---------- ---------- --------------- ------------------------- ------------- ---------------------------------------------------------------- ---------------- ------------------------ ---------------- ----------------------------------------------------------------
03-2月 -15 09.08.26.682 上午           88        459           15620                           9m2w9b8t4vs92 enq: TM - contention                                                          111                    14956                1 Lhqlp
03-2月 -15 09.08.24.682 上午           88        459           15620                           9m2w9b8t4vs92 enq: TM - contention                                                          111                    14956                1 Lhqlp
03-2月 -15 09.08.25.682 上午           88        459           15620                           9m2w9b8t4vs92 enq: TM - contention                                                          111                    14956                1 Lhqlp

SQL>

SESSION 111相关信息：
SQL> select      SAMPLE_TIME ,
2              USER_ID ,
3              SESSION_ID,
4              SESSION_SERIAL# ,
5              SQL_EXEC_START,
6              sql_id,EVENT,
7              BLOCKING_SESSION,
8              BLOCKING_SESSION_SERIAL#,
9              BLOCKING_INST_ID,
10              MACHINE
11        from v$active_session_history
12        where SESSION_SERIAL#=14956
13              and session_id=111
14              and sql_id='9m2w9b8t4vs92'
15        order by SQL_EXEC_START;

SAMPLE_TIME                       USER_ID SESSION_ID SESSION_SERIAL# SQL_EXEC_START            SQL_ID        EVENT                                                            BLOCKING_SESSION BLOCKING_SESSION_SERIAL# BLOCKING_INST_ID MACHINE
------------------------------ ---------- ---------- --------------- ------------------------- ------------- ---------------------------------------------------------------- ---------------- ------------------------ ---------------- ----------------------------------------------------------------
03-2月 -15 09.08.26.682 上午           88        111           14956                           9m2w9b8t4vs92 enq: TM - contention                                                          459                    15620                1 Lhqlp
03-2月 -15 09.08.24.682 上午           88        111           14956                           9m2w9b8t4vs92 enq: TM - contention                                                          459                    15620                1 Lhqlp
03-2月 -15 09.08.25.682 上午           88        111           14956                           9m2w9b8t4vs92 enq: TM - contention                                                          459                    15620                1 Lhqlp

结论：
在数据库上，两个SESSION在同一个时间，同一个用户88，执行了同一条语句，引起数据库死锁
相关信息如下：

SID:459.15620
时间：2015-02-03 09:08:26.841
user:88
执行语句：DELETE FROM T_CYMENT WHERE claim_payment_seq = :1

SID:111.14956
时间：2015-02-03 09:08:26.682
USER:88
客户端：Lhqlp
user: xxxxx
program: xxxxx
application name: xxxxx
执行语句：DELETE FROM T_CYMENT WHERE claim_payment_seq = :1