How to query deadlock in oracle

In Oracle, you can use the dba user to execute the "select username, lockwait, status, machine, program from v$session where sid in" statement to query the deadlock. If there is a result, the deadlock will be displayed.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query deadlock in oracle

1. Check deadlock

1) Use the dba user to execute the following statement

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

If there is an output result, then Explain that there is a deadlock, and you can see which machine is the deadlock. Field description:

Username: The database user used in the deadlock statement;

Lockwait: The status of the deadlock. If there is content, it means that it is deadlocked.

Status: Status, active means deadlocked

Machine: The machine where the deadlock statement is located.

Program: Which application does the deadlock statement mainly come from?

2) Use the dba user to execute the following statement to view the deadlocked statement.

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))

2. Solution to deadlock

1) Find the deadlocked process:

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 deadlocked process: alter system kill session ' sid,serial#'; (where sid=l.session_id)

3) If it still cannot be solved:

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

Replace sid with the deadlocked sid:

Recommended Tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query deadlock in oracle. For more information, please follow other related articles on the PHP Chinese website!