0

Executing the below query from http://www.orafaq.com/node/854

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;

I found that "SID=52 is blocking SID=730" and "SID=730 is blocking SID=52", a clear case of a deadlock. However all I could find from the v$session, v$sqlarea views is the SQL that is currently being blocked by both sessions. How do I see which SQL executed in both sessions that caused the locks.

We are using Oracle 11g (11.2.0.4) and recently noticing deadlock issues everywhere.

1 Answer 1

2

Deadlocks are automatically detected by the engine, and one of the statements will be rolled back. You can find queries by examining alert.log file (it will have a reference to trace file where detailed deadlock information is stored). It's not gonna give you the full stack of queries per sessions that contribute to the deadlock though .

The query you are using shows deadlock until it's resolved by the database. You can try checking not just v$session.sql_id, but v$session.prev_sql_id (previous query executed within the session context). If you're running Enterprise Edition (and as far as I remember Diagnostic+Tuning Packs) you may examine v$active_session_history view.

If you have an option to modify client applications that use the database, you can use DBMS_APPLICATION_INFO package to identify modules and actions that are being executed.

2
  • The deadlock exists for several minutes and doesn't get "resolved" by the database. So I googled around for the threshold and found this: jonathanlewis.wordpress.com/2013/02/22/deadlock-detection So it is a myth after all. I have checked ASH already but no dice there and the client application doesn't record its state using DBMS_APPLICATION_INFO package as well :( Commented Nov 30, 2016 at 4:57
  • There is no myth - it behaves exactly as documentation states - and yes, deadlock gets resolved. After one of the statements rolled back , there is no deadlock anymore. It's up to application to process such cases properly , sometimes rollback of the whole transaction is appropriate, sometimes not. If the application has bugs that causes deadlocks, and you cannot modify it, the only thing you can do is check for locks periodically, and kill sessions that holds lock for more than X minutes/seconds. Or if you run Enterprise version , you can use resource manager, MAX_IDLE_BLOCKER_TIME
    – a1ex07
    Commented Nov 30, 2016 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.