Oracle Locks   «Prev  Next»
Lesson 5Database deadlocks
ObjectiveIdentify an Oracle deadlock

Identifying Deadlocks in Oracle: Methodologies and Best Practices

In Oracle's Relational Database Management System (RDBMS), the locking mechanism plays a pivotal role in maintaining database integrity, especially during concurrent transactions. However, in the complex tapestry of database interactions, deadlocks can occur, severely impacting database performance and transactional integrity. Detecting these deadlocks is a critical responsibility for an Oracle Database Administrator (DBA). This document outlines the methodologies and tools available for identifying Oracle deadlocks.

Alert Log

The first line of defense in identifying a deadlock is Oracle's alert log. Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions involved. Detailed information about the deadlock, including the involved sessions and SQL statements, is written to the alert log.
  1. Location: The alert log can generally be found in the `alert_<SID>.log` file within the Oracle Diagnostic Destination directory.
  2. Content: The log contains a 'Deadlock detected' message and provides trace file references for deeper investigation.

Trace Files

When a deadlock occurs, Oracle generates specific trace files for each session involved. These files contain detailed information, such as SQL statements and locking resources, that led to the deadlock.
  1. Location: Usually in the `trace` directory under the Oracle Diagnostic Destination.
  2. Analysis: Examine the trace files to understand the deadlock sequence, SQL queries involved, and locking objects.

Dynamic Performance Views

Oracle provides various Dynamic Performance Views (V$ views) that can be queried to identify potential deadlocks:
  1. V$LOCK: Shows all locks held or requested by Oracle sessions.
  2. V$SESSION: Provides information about each current session, including any locks held.

Example SQL query to identify sessions waiting on locks:

    v$locked_object a,
    dba_objects b
    a.object_id = b.object_id;

Deadlock Graphs

Oracle trace files and even some third-party tools can generate deadlock graphs, which visually represent the locking dependencies among transactions. Understanding the deadlock graph can help you pinpoint the issue at its core.

Advanced Monitoring Tools

Advanced monitoring solutions like Oracle Enterprise Manager (OEM) also provide deadlock detection features. These tools offer real-time monitoring and alerting capabilities and can often be configured to trigger automatic actions, such as sending alerts to administrators.

Programmatic Detection

PL/SQL or Oracle’s Java-based stored procedures can be utilized to programmatically check for lock conditions, although this is generally not recommended due to the performance overhead it may impose. Identifying deadlocks is an essential aspect of maintaining an Oracle database's performance and integrity. Utilizing alert logs, trace files, Dynamic Performance Views, and advanced monitoring tools is critical for effective deadlock identification and resolution. Being well-versed in these methodologies and tools is indispensable for any Oracle DBA tasked with ensuring seamless database operations and transactional integrity.
The purpose of the Oracle locking scenario insures that all database integrity is maintained and that updates do not inadvertently overlay prior updates to the database. However, a penalty has to be paid for maintaining shared locks.

Lock Space Considerations

In Oracle, each lock requires 4 bytes of RAM storage within the Oracle instance storage pool, and large SQL SELECT statements can create SOS (short on storage) conditions that can cripple the entire database. For example, a SELECT statement that retrieves 1,000 rows into the buffer will require 4,000 bytes of lock space.

Oracle Deadlock

Multiple competing tasks can also cause a condition called the "deadly embrace," or a database deadlock. A deadlock condition occurs when two tasks are waiting on resources that the other task has locked. The following series of images shows how a deadlock develops.

Database Deadlock Occurence

1) Task grabs row 123 with an exclusive lock.
1) Task grabs row 123 with an exclusive lock.

2) Task B grabs row number 456 with an exclusive lock.
2) Task B grabs row number 456 with an exclusive lock.

3) Task A row requests row 456, and begins waiting for Task B to release the lock.
3) Task A row requests row 456, and begins waiting for Task B to release the lock.

4) Task B now requests row 123.
4) Task B now requests row 123. Since Task A is waiting for task B to release row 345.both jobs will wait perpetually for the other task to release their lock.

5) Fortunately, Oracle will kill task B with an ORA-60 error
5) Fortunately, Oracle will kill task B with an ORA-60 error.

Oracle releases Database Deadlock

In reality, of course, Oracle will release the database deadlock by aborting the task that caused the deadlock, in this case task A. Oracle's Lock Manager will detect the deadly embrace after a task has been waiting and will abort the task with the ORA-60 "deadlock detected while waiting for resource" message. Remember that it is impossible for a single task to cause a deadlock and two tasks must be involved. Now that you know what a deadlock is, the next lesson will show you how to prevent it from happening.