How Does Oracle handle deadlocks?

When a deadlock occurs in the Oracle database, follow these steps:

Step 1:Try to delete in sqlplus using SQL commandsIf the deletion is successful, everything is fine! However, if you want to delete a session with a deadlock through a command line or using an Oracle management tool, oracle will only mark the session as killed but cannot clear it, 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 quikSQL> select xidusn, object_id, session_id, locked_mode from v $ locked_object; -- query the deadlock object, obtain its SESSION_IDXIDUSN OBJECT_ID SESSION_ID LOCKED_MODE ------------ ------------- 10 30724 29 310 30649 29 3SQL> select username, sid, serial # from v $ session where sid = 29; -- view the serial # USERNAME sid SERIAL # -------------------------------- ------------ QUIK 29 571_ SQL> alter system kill session '2017 07 'According to the SID obtained in the previous step; -- delete the process, if the session has been deleted, the ora-00031 error will be reported; otherwise oracle will mark the session as killed status, wait for a period of time to see whether it will automatically disappear, such as long time does not disappear, follow these steps to alter system kill session '2017 07 'ora-29,571: session marked for killSQL> select pro. spid from v $ session ses, v $ process pro where ses. sid = 29 and ses. paddr = pro. addr; -- view the spid to delete the process SPID------------2273286 from the Operating System

Step 2:Go to the operating system to delete the processIn this example, the operating system is IBM aix.

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

Login: root -- enter the username root's Password: -- enter the 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.254Last 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 14:38:24 grep 2273286 oracle 2273286 1 0-oracleQUIK (LOCAL = NO) # kill-9 2273286 -- delete a process, perform this operation with caution, do not write the wrong process number. If the key process of oracle is deleted, the database will crash! # Ps-ef | grep 2273286 -- View root 2289864 again 0 17:07:15 pts/1 grep 2273286For Windows, at the DOS Prompt: orakill sid spidFor UNIX at the command line> kill-9 spid