Introduction of Query and processing method for Oracle deadlock problem

First, the phenomenon of database deadlock

First confirm the login interface SYS/DBA

Is that the program does not return, like the software card dead

second, the principle of deadlock

Is that an operation completes the uncommitted completion commit, causing the data in the subsequent queue to wait so that the deadlock is caused.

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

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 that is deadlocked.

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. The user may not know which sentence is the deadlock-generating statement. 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:

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:

Alter system kill session ' sid,serial# '; (of which sid=l.session_id)

3) If it is not resolved:

Select Pro.spid from V$session ses,v$process Pro where ses.sid=xx and ses.paddr=pro.addr where the SID is replaced by a deadlock: Exit Ps-ef|grep SPID where the SPID is the process number of this process, kill the Oracle process KILL-9 "The SPID just identified" on the Windows platform, can be my orakill.

4) Query Deadlock statement

Select A.sql_text, B.username, c.object_id, c.session_id, b.serial#, C.oracle_username,c.os_user_name,c.process,

''''|| c.session_id| | ', ' | | b.serial#| | "'"

From V$sql A, V$session B, V$locked_object C

where A.hash_value = B.sql_hash_value and

B.sid = c.session_id

Streamline processing steps

--1, looking for locked table object_id

Select object_id from all_objects where object_name = UPPER (' table_name ') and object_type = ' table '

--2, object_id to find the session ID of the locked object according to the 1th step

Select session_id from v$locked_object where object_id = 1779474

--3, session_id search serial# according to the 2nd step

Select sid,serial# from v$session where sid = 284

--4, kill the process according to SESSION_ID and serial#.

Alter system kill session ' sid,serial# '

--Find the action statement on the locked object

Select Sql_text from V$sqlarea where address = '--address ' sql_address field in V$session