Deadlock Handling Method in Oracle

The Oracle database deadlock can be resolved by following the following processing steps:

Step 1: Try to delete through sql command in sqlplus. If you can delete successfully, everything will be fine! But usually, when deadlock occurs, if you want to delete the deadlocked session through the command line or through the management tools of Oracle, oracle will only mark the session as killed, but it cannot be removed, so you often need to delete it at the operating system level through Step 2!

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as quik
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object; -- Check the deadlocked object and get its SESSION_ID
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
10 30724 29 3
10 30649 29 3
SQL> select username,sid,serial# from v$session where sid=29; -- According to the obtained in the previous step sid View its serial# No. 
USERNAME SID SERIAL#
------------------------------ ---------- ----------
QUIK 29 57107
SQL> alter system kill session '29,57107'; -- Delete the process, if it has already been deleted, it will be reported ora-00031 The mistake; Otherwise oracle Will set the session Marked as killed Status, wait 1 See if it will disappear automatically for a period of time. If it can't disappear for a long time, it needs to do subsequent steps 
alter system kill session '29,57107'
ORA-00031: session marked for kill
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=29 and ses.paddr=pro.addr; -- View spid Number so that processes can be deleted in the operating system according to the process number 
SPID
------------
2273286

Step 2: Enter the operating system for the deletion process, which is IBM aix for this example.

Microsoft Windows XP [ Version  5.1.2600]
(C)  Copyright reserved  1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>telnet 10.73.52.7 -- Pass telnet Log on to the database server remotely 
AIX Version 5
(C) Copyrights by IBM and by others 1982, 2005.

login: root -- Enter User Name 
root's Password: -- Enter password 
*******************************************************************************
* *
* *
* Welcome to AIX Version 5.3! *
* *
* *
* Please see the README file in /usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
*******************************************************************************
Last unsuccessful login: Fri Apr 23 14:42:57 BEIDT 2010 on /dev/pts/1 from 10.73
.52.254
Last login: Fri Apr 23 15:27:50 BEIDT 2010 on /dev/pts/2 from 10.73.52.254
# ps -ef|grep 2273286 -- View process details 
root 2289864 2494636 0 17:07:15 pts/1 0:00 grep 2273286
oracle 2273286 1 0 14:38:24 - 0:21 oracleQUIK (LOCAL=NO)
# kill -9 2273286 -- Delete the process, be careful, don't write the wrong process number, if oracle If the key process is deleted, the database will crash! 
# ps -ef|grep 2273286 -- View again 
root 2289864 2494636 0 17:07:15 pts/1 0:00 grep 2273286
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill  In fact, in fact, the 9 spid