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.
Contribute to this site maintenance !
This is a self hosted site, on own hardware and Internet connection. The old, down to earth way 🙂. If you think that you found something useful here please contribute. Choose the form below (default 1 EUR) or donate using Bitcoin (default 0.0001 BTC) using the QR code. Thank you !