Shulou(Shulou.com)06/01 Report--

ORACLE Deadlock (ORA-00060) Troubleshooting General Methods

[Background]

This morning's inspection found that SAP PRD generated a deadlock:

Sun Jun 29 10:30:36 2014

ORA-00060: Deadlock detected.

More info in file /oracle/PRD/saptrace/diag/rdbms/prd/PRD/trace/PRD_ora_2065024.trc.

Found a good troubleshooting blog on the Internet, although not produced by SAP system, but also has a very good reference value, turn to this and carry out some improvement and processing, as follows:

[Fault]:

Deadlocks occur when two Java-written background batches execute simultaneously. (My case is SAP PRD environment, but the underlying database is Oracle as well.)

[Investigation Method]:

Find the locked object v$locked_object by querying the view, and judge its lock type according to its locked_mode

Query SQL statement:

SET PAGESIZE 2000 LINESIZE 1000;

col XIDUSN format 99;

col OBJECT_ID format 99999999;

col OWNER format a8;

col OBJECT_NAME format a25;

col SESSION_ID format 99999;

col ORACLE_USERNAME format a8;

col OS_USER_NAME format a8;

col PROCESS formaT 999999;

COL LOCKE format a4;

select l.xidusn, l.object_id, o.owner, o.object_name,

l.session_id, l.oracle_username, l.os_user_name, l.process,

decode(l.locked_mode, 0, '',

1, 'NULL',

2, '(SS)',

3, '(SX)',

4, '(S)',

5, '(SSX)',

6, '(X)',

'??? ') locked_mode

from v$locked_object l, dba_objects o

where l.object_id = o.object_id;

Judge the query result and find that two sessions exclude the data rows of the same table.

Query the view v$sqltext with the following statement to get the SQL statement currently executing and the session executing the SQL statement

select username, osuser, machine, terminal, program,

sid, serial#, status, sql_address, sql_text

from v$session ss, v$sqltext sq

where type = 'USER'

and ss.sql_address = sq.address

order by ss.sid, ss.serial#, sq.piece;

You can find two SQL statements updating the same data row in the same table.

These two SQL statements allow you to locate the code in your Java program that caused the problem.

[Cause Analysis]:

After analyzing Java code, it was found that there was an operation that iterated line by line according to the primary key of the data row.

Unfortunately, the list of primary keys generated from KeySet () is not sorted before looping, resulting in random execution order for each loop.

For example, suppose two sessions both want to process data rows A,B,C,D, it is likely that session1 processed A,B first,

At this point, session2 has just finished processing C,D. In this way, C,D, which session1 wants to continue processing, cannot continue because it is locked by session2.

Session2 wants to process A and B are also locked by session1, session2 cannot continue, and there is no way to terminate the two sessions.

TRACE file with ORACLE

After Oracle detects a deadlock, it outputs the following warning message in the alert_[SID].log file: ORA-00060: Deadlock detected.

And prompt to check the corresponding *.trc file. By analyzing the *.trc file you can see the details of deadlocks,

Here is an example of a *.trc file:

*** 2012-01-09 20:11:22.379

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-000a0006-0000f48f 65 101 X 64 102 X

TX-0007000f-0000d8a3 64 102 X 65 101 X

session 101: DID 0001-0041-00000002 session 102: DID 0001-0040-00000002

session 102: DID 0001-0040-00000002 session 101: DID 0001-0041-00000002

Note: This specifies the ID of the two sessions where the deadlock occurred.

Rows waited on:

Session 101: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAA

(dictionary objn - 561498, file - 5, block - 1247925, slot - 0)

Session 102: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAI

(dictionary objn - 561498, file - 5, block - 1247925, slot - 8)

----- Information for the OTHER waiting sessions -----

Session 102:

sid: 102 ser: 2 audsid: 25260645 user: 87/USR_BAT flags: 0x41

pid: 64 O/S info: user: ora_1, term: UNKNOWN, ospid: 5915

image: oracle@pcXX

client details:

O/S info: user: root, term: unknown, ospid: 1234

machine: pcXX program: JDBC Thin Client

application name: JDBC Thin Client, hash value=2546894660

current SQL:

UPDATE XXXX SET XXXX Note: This is the SQL statement that caused the deadlock 1

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

Information for THIS session:

*** 2012-01-09 20:11:22.530

----- Current SQL Statement for this session (sql_id=b0qn65w78t10b) -----

UPDATE XXXX SET XXXX Note: This is the SQL statement that caused the deadlock 2

===================================================

※ The storage path of log file and trc file depends on Oracle installation path. File search function can be used.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.