Detailed analysis of the Oracle deadlock analysis process

Oracle deadlock analysis

There are three methods to handle deadlocks.

1. Advance prediction

2. Resource classification

3. Release after detection

I know that ORACLE MYSQL uses the third row lock level.

Here we will analyze an ORACLE deadlock. First, a deadlock will certainly generate a TRACE file, which will record a lot of information such:

Deadlock graph:

--------- Blocker (s) -------- --------- Waiter (s )---------

Resource Name process session holds waits

TX-0058000f-0000b473 649 1204X651 1252 X

TX-0019001c-0004e0b0 651 1252X649 1204 X

The process and session id are provided here.

Rows waited on:

Session 1204: obj-rowid = 0003D942-AAA9lCAAEAADgaNAAI

(Dictionary objn-252226, file-4, block-919181, slot-8)

Session 1252: obj-rowid = 0003D942-AAA9lCAAEAADgaNAAa

(Dictionary objn-252226, file-4, block-919181, slot-26)

The row that causes the deadlock is shown here.

The statement for triggering the deadlock session 1252 is also provided.

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

Session 1252:

Sid: 1252 ser: 35883 audsid: 7170593 user: 235/FEECORESV

Flags: (0x100045) USR/-flags_idl: (0x1) BSY /-/-/-/-/-

Flags2: (0x40009)-/INC

Pid: 651 Io/S info: user: oracle, term: UNKNOWN, ospid: 13035

Image: oracle @ oratest11

Client details:

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

Machine: autobots program: JDBC Thin Client

Application name: JDBC Thin Client, hash value = 2546894660

Current SQL:

UPDATE *******

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

Information for THIS session:

----- Current SQL Statement for this session (SQL _id = 3vh5sc7pgtrjy )-----

UPDATE *******

So here we can probably analyze

A: 1204 get the AAA9lCAAEAADgaNAAa row lock B: 1252 get the AAA9lCAAEAADgaNAAI row Lock

C: 1204. If AAA9lCAAEAADgaNAAI is required, wait for D: 1252. If AAA9lCAAEAADgaNAAa is required, the deadlock 1204 rollback is triggered.

Then trace will give the 1204 C wait time and transaction information.

SO: 0xee1fcd10, type: 4, owner: 0xf031e750, flag: INIT/-/0x00 if: 0x3 c: 0x3

Proc = 0xf031e750, name = session, file = ksu. h LINE: 12624, pg = 0

(Session) sid: 1204 ser: 2443 trans: 0xe9221180, creator: 0xf031e750

Flags: (0x100045) USR/-flags_idl: (0x1) BSY /-/-/-/-/-

Flags2: (0x40009)-/INC

DID:, short-term DID:

Txn branch: (nil)

Oct: 6, prv: 0, SQL: 0xf25d2278, psql: 0xc4000088, user: 235/FEECORESV

Ksuxds FALSE at location: 0

Service name: SYS $ USERS

Current Wait Stack:

0: waiting for 'enq: TX-row lock contention'

Name | mode = 0x54580006, usn <16 | slot = 0x19001c, sequence = 0x4e0b0

Wait_id = 33 seq_num = 34 snap_id = 1

Wait times: snap = 3.001739 sec, exc = 3.001739 sec, total = 3.001739 sec

Wait times: max = infinite, heur = 3.001739 sec

Wait counts: cballs = 1 OS = 1

In_wait = 1 iflags = 0x15a0

Then the waiting information that causes him to wait for the session is provided, which is not provided here. Of course there will be many classes later, but the key is the above

However, there is no execution process for the displayed items. To see the complete statements, we need to mine logs as follows:

1204:

Set transaction read write;

Select * from TEST. TEST where ROWID = 'aaa9lcaaeaadganaaa' for update;

Commit;

1252:

Set transaction read write;

Select * from TEST. TEST where ROWID = 'aaa9lcaaeaadganaai' for update;

Update TEST. TEST set "STATUS" = 'success', "DETAIL" = 'execution successfully', "RAW_UPDATE_TIME" = TO_TIMESTAMP ('19-SEP-16 01.27.25.714715 PM ') where "IDENTITY" = '000000' and "STATUS" = 'processing' and "DETAIL" is null and "RAW_UPDATE_TIME" = TO_TIMESTAMP ('19-SEP-16 01.27.24.611036 PM ') and ROWID = 'aaa9lcaaeaadganaaa ';

Commit;

In this way, we can clearly see that update 1204 is not executed, and the deadlock rollback is triggered.