Investigate #Oracle #deadlocks

Sometimes we may end up having some application resources competing for the same resource on our Oracle database. This is usually the case when some exclusive resource must be allocated or updated as in the case of database based locking mechanisms.

If there are code issues we may end up in a deadlock situation when application resources are locked in a cycle of requiring exclusivity over some resource.

In this case sessions in Oracle will get blocked by other sessions.

To be able to investigate the issue at the application level it is very useful to understand what application call is blocked and who is blocking it.

Detect block sessions and who is the owner
In case of a deadlock we can find out, by running as sysdba the following query, who is the blocked session , who is the blocker session and which are the owners of the sessions:

SELECT blocker.username blocker, blocker.sid blocker_session
     , blocked.username blocked, blocked.sid blocked_session
  FROM v$session blocked
  JOIN v$session blocker
  ON (blocked.blocking_session = blocker.sid)
 WHERE blocked.wait_class = 'Application'
   AND blocked.event='enq: UL - contention';

In case no deadlock is present the above query will return nothing.

In case one or more deadlocks are present the return rows will contain all the info we need to see who is waiting for who.

Further on we can investigate using the blocker session ID to see why that session is not terminating and who is the offending SQL query.