Home > Article > Database > 如何通过trace诊断ORA-00060 Deadlock Type?

如何通过trace诊断ORA-00060 Deadlock Type?

WBOYOriginal: 2016-06-07 17:36:001101browse

今天是2014-03-12，在进行数据库性能调优过程中，突然翻到了之前一封邮件，信息如下：

从日志中看，发现存在行级排他锁存在争用以致产生了死锁。

问题描述：

*** 2013-12-02 17:03:10.148

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-000b002b-002be2d3 61 958 X 141 876 X

TX-0009001c-000d5ffa 141 876 X 61 958 X

session 958: DID 0001-003D-00001BC1 session 876: DID 0001-008D-0000B424

session 876: DID 0001-008D-0000B424 session 958: DID 0001-003D-00001BC1

Rows waited on:

Session 876: obj - rowid = 0000DE75 - AAAN51AArAAAA6mAAO

(dictionary objn - 56949, file - 43, block - 3750, slot - 14)

Session 958: obj - rowid = 00014F07 - AAAU8HABXAAA7BqAAI

(dictionary objn - 85767, file - 87, block - 241770, slot - 8)

Information on the OTHER waiting sessions:

Session 876:

sid: 876 ser: 38057 audsid: 7879846 user: 47/EPMFRAMEWORK

flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x8)

pid: 141 O/S info: user: orarep, term: UNKNOWN, ospid: 11195

image: oracle@pmsdb2

O/S info: user: webpms2, term: unknown, ospid: 1234, machine: pmsrep1

program: JDBC Thin Client

application name: JDBC Thin Client, hash value=2546894660

Current SQL Statement:

UPDATE AP_COLL_INFO_T T SET T.PROCINST_ID = NULL, T.IF_PROJECT_INHERITED = '0' WHERE T.INVEST_SNO = :1

End of information on OTHER waiting sessions.

Current SQL statement for this session:

UPDATE AP_PROJECT_INFO_EDITION_T T SET T.IF_INHERITED='1' WHERE T.AP_PRJ_SNO=:1

在上面可以获得很多可以帮助诊断问题的信息，，该信息显示由于应用逻辑错误，导致了死锁的产生。

另外还可以通过相关sql获得一些简要信息：

eg：

2、分解rowid获得产生死锁等待的对象信息如下：
SQL> select a.owner,
2 a.object_name,
3 a.object_type,
4 a.last_ddl_time,
5 a.created,
6 a.status,
7 b.object,
8 b.fno,
9 b.rownu,
10 b.block
11 from (select dbms_rowid.rowid_object('AAAU8HABXAAA7BqAAI') object,
12 dbms_rowid.rowid_relative_fno('AAAU8HABXAAA7BqAAI') fno,
13 dbms_rowid.rowid_row_number('AAAU8HABXAAA7BqAAI') rowNU,
14 dbms_rowid.rowid_block_number('AAAU8HABXAAA7BqAAI') block
15 from dual) b,
16 dba_objects a
17 where a.object_id = b.object;
OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME CREATED STATUS OBJECT FNO ROWNU BLOCK
------------------------------ -------------------------------------------------------------------------------- ------------------- ------------- ----------- ------- ---------- ---------- ---------- ----------
EPMFRAMEWORK AP_PROJECT_INFO_EDITION_T TABLE 2013-11-27 11 2012-06-05 VALID 85767 87 8 241770
SQL>
SQL> select a.owner,
2 a.object_name,
3 a.object_type,
4 a.last_ddl_time,
5 a.created,
6 a.status,
7 b.object,
8 b.fno,
9 b.rownu,
10 b.block
11 from (select dbms_rowid.rowid_object('AAAN51AArAAAA6mAAO') object,
12 dbms_rowid.rowid_relative_fno('AAAN51AArAAAA6mAAO') fno,
13 dbms_rowid.rowid_row_number('AAAN51AArAAAA6mAAO') rowNU,
14 dbms_rowid.rowid_block_number('AAAN51AArAAAA6mAAO') block
15 from dual) b,
16 dba_objects a
17 where a.object_id = b.object;
OWNER OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME CREATED STATUS OBJECT FNO ROWNU BLOCK
------------------------------ -------------------------------------------------------------------------------- ------------------- ------------- ----------- ------- ---------- ---------- ---------- ----------
EPMFRAMEWORK AP_COLL_INFO_T TABLE 2012-04-01 21 2011-10-05 VALID 56949 43 14 3750
SQL>
3、导致此问题的sql语句为：
SQL> SELECT B.SQL_ID,SQL_FULLTEXT,B.EVENT,B.SAMPLE_TIME,B.SESSION_ID FROM V$sqL A INNER JOIN (
2 select session_id, session_serial#, sql_id, event, SAMPLE_TIME
3 from dba_hist_active_sess_history
4 where event = 'enq: TX - row lock contention'
5 and sample_time >
6 to_date('2013-12-02 17:02:00', 'YYYY-MM-DD HH24:MI:SS') ) B
7 ON A.SQL_ID=B.SQL_ID;
SQL_ID SQL_FULLTEXT EVENT SAMPLE_TIME SESSION_ID
------------- -------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
9bwcgrn96cx62 INSERT INTO rt_actinst_data(instance_id,name,type,data,merge_type,rdata_app)VALUES(:1,:2,:3,:4,:5,:6) enq: TX - row lock contention 02-12月-13 05.03.21.158 下午 959
9bwcgrn96cx62 INSERT INTO rt_actinst_data(instance_id,name,type,data,merge_type,rdata_app)VALUES(:1,:2,:3,:4,:5,:6) enq: TX - row lock contention 02-12月-13 05.03.11.158 下午 959
1npq5jnavnc0k UPDATE AP_PROJECT_INFO_EDITION_T T SET T.IF_INHERITED='1' WHERE T.AP_PRJ_SNO=:1 enq: TX - row lock contention 02-12月-13 05.03.01.153 下午 958
9bwcgrn96cx62 INSERT INTO rt_actinst_data(instance_id,name,type,data,merge_type,rdata_app)VALUES(:1,:2,:3,:4,:5,:6) enq: TX - row lock contention 02-12月-13 05.03.01.153 下午 959
1npq5jnavnc0k UPDATE AP_PROJECT_INFO_EDITION_T T SET T.IF_INHERITED='1' WHERE T.AP_PRJ_SNO=:1 enq: TX - row lock contention 02-12月-13 05.02.51.148 下午 958
1npq5jnavnc0k UPDATE AP_PROJECT_INFO_EDITION_T T SET T.IF_INHERITED='1' WHERE T.AP_PRJ_SNO=:1 enq: TX - row lock contention 02-12月-13 05.02.41.148 下午 958
1npq5jnavnc0k UPDATE AP_PROJECT_INFO_EDITION_T T SET T.IF_INHERITED='1' WHERE T.AP_PRJ_SNO=:1 enq: TX - row lock contention 02-12月-13 05.02.31.148 下午 958
7 rows selected
SQL>

我要说的是在trace中可以获得一个deadlock graph:

看个例子：

如何通过trace诊断ORA-00060 Deadlock Type?

那么本次锁的最后组合为：tx x x；

最后总结一下（一见明了）：

如何通过trace诊断ORA-00060 Deadlock Type?

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Oracle 10g 创建 DBLinkNext article：CentOS 6.4下安装MySQL后重置root密码方法

See more

如何通过trace诊断ORA-00060 Deadlock Type?

Related articles