How to query deadlock in oracle

In Oracle, you can use the select statement to query deadlocks. This statement is used for simple data query. The syntax is "select * from v$session where sid in (select session_id from v$locked_object)".

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 it is dead Lock.

• Status: Status, active means deadlocked

• Machine: The machine where the deadlock statement is located.

• Program: From which application the statements that generate deadlocks mainly come from

2) Use the dba user to execute the following statements, you can view the deadlock Lock 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)

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!