Detection and processing of Oracle deadlock

--Deadlock Query statement

The code is as follows

Copy Code

Select Bs.username "Blocking user", bs.username "DB user", Ws.username "Waiting User", BS. Sid "Sid", WS. SID "Wsid", bs.serial# "serial#", bs.sql_address "address", Bs.sql_hash_value "SQL Hash", bs.program "Blocking App", Ws.program "Waiting App", bs.machine "Blocking machine", Ws.machine "Waiting Machine", Bs.osuser "Blocking OS User", Ws.osuser "Waiting OS User", bs.serial# "serial#", ws.serial# "wserial#", DECODE (wk. TYPE, ' MR ', ' Media Recovery ', ' RT ', ' Redo Thread ', ' UN ', ' USER Name ', ' TX ', ' Transaction ', ' TM ', ' DML ', ' UL ', ' pl/sql USER LOCK ', ' DX ', ' Distributed xaction ', ' CF ', ' Control FILE ', ' Is ', ' Instance state ', ' FS ', ' FILE SET ', ' IR ', ' Instance Recovery ', ' ST ', ' Disk space Transaction ', ' TS ', ' Temp Segment ', ' IV ', ' Library Cache invalidation ', ' LS ', ' LOG START OR Switch ', ' RW ', ' ROW wait ', ' SQ ', ' Sequence number ', ' TE ', ' Extend TABLE ', ' TT ', ' Temp TABLE ', Wk. TYPE ) Lock_type, DECODE (Hk.lmode, 0, ' None ', 1, ' NULL ', 2, ' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' SHARE ', 5, ' S/row-x (SSX) ', 6, ' EXCLUSIVE ', To_char (Hk.lmode) ) Mode_held, DECODE (Wk.request, 0, ' None ', 1, ' NULL ', 2, ' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' SHARE ', 5, ' S/row-x (SSX) ', 6, ' EXCLUSIVE ', To_char (Wk.request) ) mode_requested, To_char (HK.ID1) lock_id1, To_char (HK.ID2) Lock_id2, DECODE (HK. block, 0, ' not Blocking ',/**//* not Blocking the other processes * 1, ' Blocking ',/**//* this lock blocks the other processes * * 2, ' Global ',/**//* this lock's global, so we can ' t-tell * * To_char (HK. Block) ) blocking_others From V$lock HK, v$session BS, V$lock wk, v$session ws WHERE HK. Block = 1 and Hk.lmode!= 0 and Hk.lmode!= 1 and wk.request!= 0 and WK. TYPE (+) = HK. TYPE and WK.ID1 (+) = Hk.id1 and Wk.id2 (+) = Hk.id2 and HK. SID = BS. SID (+) and WK. SID = ws. SID (+) and (Bs.username is not NULL) and (Bs.username <> ' SYSTEM ') and (Bs.username <> ' SYS ') Order by 1;

Select statement that a deadlock occurred in the query

The code is as follows	Copy Code
Select Sql_text from V$sql where Hash_value in ( Select Sql_hash_value from V$session where SID in (select session_id from V$locked_object) )

About the method of checking database deadlock

First, the phenomenon of database deadlock procedures in the execution process, click OK or save the button, the program has no response, there is no error.

Second, the principle of deadlock

When you perform an update or delete operation on a column of a table in a database, the statement does not mention
Hand, another statement that updates the data for this column will be in the waiting state when executed,
At this point the phenomenon is that the statement has been executed, but has not executed successfully, there is no error.

The method of locating dead lock

By checking the database table, you can check which statement is deadlocked, which is the machine that produces the deadlock.

1 Execute the following statement with the DBA user

The code is as follows	Copy Code
Select Username,lockwait,status,machine,program from v$session where SID In (select session_id from V$locked_object)

If there is a result of the output, then there is a deadlock, and can see the deadlock machine is which. Field Description:
Username: The database user used by the deadlock statement;
Lockwait: The state of the deadlock, if there is content to indicate a deadlock.
Status: State, active indicates deadlock
Machine: The machine where the deadlock statement resides.
Program: Which application is the main source of the statement that generated the deadlock.

2 Execute the following statement with the DBA user to view the statement of the deadlock

Select Sql_text from V$sql where Hash_value in
(select Sql_hash_value from v$session where SID in
(select session_id from V$locked_object))

Four, deadlock resolution method

In general, as long as the statement that will produce the deadlock can be committed, but in the actual execution process. Users can
I don't know which sentence is the one that produces the deadlock. You can turn the program off and restart it.
This problem is often encountered during Oracle's use, so a solution is also summarized.

1 Find the process of deadlock:

The code is as follows	Copy Code
Sqlplus "/as sysdba" (Sys/change_on_install) SELECT s.username,l.object_id,l.session_id,s.serial#,l.oracle_username,l.os_user_name,l.process From V$locked_object l,v$session S WHERE L.session_id=s.sid;

2 Kill the process of the deadlock:

The code is as follows	Copy Code
Alter system kill session ' sid,serial# '; (of which sid=l.session_id)

3) If it is not resolved:

The code is as follows	Copy Code
Select Pro.spid from v$session ses, v$process Pro where ses.sid=xx and ses.paddr=pro.addr;

Where the SID replaces the SID with the deadlock:

The code is as follows	Copy Code
Exit Ps-ef|grep spid

The

Where the SPID is the process number of this process, kill the Oracle process.