Common Methods for Solving oracle deadlocks (tried and tested) and oracle deadlocks

Common Methods for Solving oracle deadlocks (tried and tested) and oracle deadlocks

-- 1. query the locked status

Select object_name, machine, s. sid, s. serial #

From v $ locked_object l, dba_objects o, v $ session s

Where l. object_id = o. object_id and l. session_id = s. sid;

-- 2. Use the following statement to kill the deadlock process.

Alter system kill session '123'; -- (where 24,111 is the sid found above, serial #)

-- 3. If the preceding kill fails, query the OS-level threads.

Select spid, osuser, s. program

From v $ session s, v $ process p

Where s. paddr = p. addr and s. sid = 24      -- 24 is the sid above

-- 4. delete a thread at the OS level 

 -- 1) run the command as root on unix:

     # Kill-9 12345 (that is, the spid obtained in step 1)

 -- 2) use orakill to kill a thread in windows (unix also applies). orakill is an executable Command provided by oracle. The syntax is as follows:

 Orakill sid thread

 -- Where:

 -- Sid: indicates that the process to be killed belongsInstance name

 -- Thread: the ID of the thread to be killed, that is, the spid obtained in step 1.

 

Oracle deadlock

This problem is often encountered during the use of oracle, so I also summarized some solutions :)
1) Find the deadlock process:
Sqlplus "/as sysdba"
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 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;
The sid is replaced by the sid of the deadlock.
Exit
Ps-ef | grep spid
Spid is the process Number of the process and kill the Oracle process.

Oracle often deadlocks and locks some tables in the database. What are the causes of oracle deadlocks?

Generally, only lock timeout occurs. When a process needs to access a database table or field, and another program is executing a lock access (such as modifying data), the process will wait, when the lock has not been lifted for a long time, the lock will time out and report a system error and refuse to execute the corresponding SQL operation. There are few deadlocks. For example, a process needs to access two resources (database tables or fields). When a resource is obtained, the process locks it and waits for the next resource to be idle, at this time, if another process requires two resources, and the second resource has been acquired and locked, the deadlock will occur because the current process locks the first resource and waits for the second resource, another process locks the second resource and waits for the first resource. Both processes will never be satisfied.
Erp100.com