Oracle deadlock handling

How should we solve the Oracle deadlock problem? Many people may have such questions. The following describes how to handle the Oracle deadlock for your reference.

Generally, you only need to submit the statement that generates an Oracle deadlock, but in the actual execution process. The user may not know which statement is used to generate a deadlock. You can close the program and restart it.
This problem is often encountered during the use of Oracle, so a few solutions are also summarized.

1) Find the process of Oracle 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 Oracle deadlock process:

 
 

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

3) if the problem persists:

 
 

  
  
select pro.spid from v$session ses,  
  
  
v$process pro where ses.sid=XX and   
  
  
ses.paddr=pro.addr; 
 
 

Replace the sid with the sid of the deadlock:

 
 

  
  
exit  
  
  
ps -ef|grep spid 
 
 

Spid is the process Number of the process and kill the Oracle process.

Statement of Oracle paging Query

Oracle sqlplus statement Editing Command

Statement of Oracle cyclic statements

Oracle user syntax Modification

How to check the oracle deadlock