How to identify and troubleshoot deadlocks in Oracle?

Introduction
What are Deadlocks?
How to Produce a Deadlock Scenario
How to Identify Deadlocks
Troubleshooting Deadlocks
Conclusion

Introduction

Deadlocks in Oracle databases can significantly impact performance and user experience, as they prevent some transactions from completing. This blog post will guide you through understanding, identifying, and troubleshooting deadlocks in Oracle databases

What are Deadlocks?

A deadlock occurs when two or more transactions are waiting for each other to release locks on resources, causing a cyclic dependency that prevents any of them from proceeding. In Oracle, deadlocks are automatically detected, and the database resolves them by rolling back one of the transactions to break the cycle.

How to Produce a Deadlock Scenario

To demonstrate a deadlock, we'll use a simple example with two tables and two sessions.

Setup

Create two tables and insert some data:

CREATE TABLE table1 (id NUMBER, value VARCHAR2(50));
CREATE TABLE table2 (id NUMBER, value VARCHAR2(50));

INSERT INTO table1 VALUES (1, 'DBDOCS A');
INSERT INTO table2 VALUES (1, 'DBDOCS B');

COMMIT;

Simulate Deadlock

Open two SQL*Plus sessions and execute the following commands.
session 1:

-- Lock a row in table1
UPDATE table1 SET value = 'X' WHERE id = 1;

session 2:

-- Lock a row in table2
UPDATE table2 SET value = 'Y' WHERE id = 1;

session 1:

-- Try to lock a row in table2 (will wait for Session 2 to release the lock)
UPDATE table2 SET value = 'Z' WHERE id = 1;

session 2:

-- Try to lock a row in table1 (will wait for Session 1 to release the lock)
UPDATE table1 SET value = 'W' WHERE id = 1;

At this point, both sessions are waiting for each other to release locks, creating a deadlock.

How to Identify Deadlocks

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions. To identify deadlocks, you can use the following methods:

Method 1: Alert Log

Oracle records deadlock information in the alert log. You can view the alert log to identify deadlocks.

# Check the alert log for deadlock information
tail -f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log


2024-06-01T10:04:52.972510-04:00
DBDOCSPDB(3):Errors in file /opt/oracle/diag/rdbms/dbdocs/dbdocs/trace/dbdocs_ora_3602.trc:
2024-06-01T10:04:53.215029-04:00
DBDOCSPDB(3):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file /opt/oracle/diag/rdbms/dbdocs/dbdocs/trace/dbdocs_ora_3602.trc.

Method 2: Trace Files

Oracle generates trace files for deadlocks in the user_dump_dest or diagnostic_dest directory. These files provide detailed information about the deadlock, including the SQL statements involved and the session IDs.

# Locate and view the trace file
cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
ls -ltr *.trc
cat 

The trace file will contain information like:

2024-06-01 10:04:52.900*:ksq.c@13216:ksqdld_hdr_dump():
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00010006-0000027B-679AC9F0-00000000         54       9     X        27628      86     272           X  55042
TX-00090017-00000257-679AC9F0-00000000         86     272     X        55042      54       9           X  27628

----- Information for waiting sessions -----
Session 9:
  sid: 9 ser: 27628 audsid: 60016 user: 109/DBDOCS_USER
  pdb: 3/DBDOCSPDB
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-
  pid: 54 O/S info: user: oracle, term: UNKNOWN, ospid: 3602
    image: oracle@dbdocs  client details:
    O/S info: user: oracle, term: pts/0, ospid: 3600
    machine: dbdocs program: sqlplus@dbdocs (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE table2 SET value = 'Z' WHERE id = 1

Session 272:
  sid: 272 ser: 55042 audsid: 60021 user: 109/DBDOCS_USER
  pdb: 3/DBDOCSPDB
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-/-
  pid: 86 O/S info: user: oracle, term: UNKNOWN, ospid: 3820
    image: oracle@dbdocs  client details:
    O/S info: user: oracle, term: pts/1, ospid: 3818
    machine: dbdocs program: sqlplus@dbdocs (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE table1 SET value = 'W' WHERE id = 1

----- End of information for waiting sessions -----

Method 3: Dynamic Performance Views

Oracle automatically detects deadlocks and logs detailed information about them. You can query this information using the following SQL query:

select
    -- Session causing the block
    blockers.blocker_instance_id as blocker_instance_id,
    blocker.sid                  as blocker_sid,
    blocker.serial#              as blocker_serial#,
    blocker.username             as blocker_username,
    blocker.status               as blocker_status,
    blocker.machine              as blocker_machine,
    blocker.program              as blocker_program,
    blocker.sql_id               as blocker_sql_id,
    blocker.sql_child_number     as blocker_sql_child_number,
    blocker.prev_sql_id          as blocker_prev_sql_id,
    blocker.prev_child_number    as blocker_prev_child_number,
    '->'                        as is_blocking,
    -- Sesssion being blocked
    blocked.sid                  as blocked_sid,
    blocked.serial#              as blocked_serial#,
    blocked.username             as blocked_username,
    blocked.status               as blocked_status,
    blocked.machine              as blocked_machine,
    blocked.program              as blocked_program,
    blocked.blocking_session     as blocked_blocking_session,
    blocked.sql_id               as blocked_sql_id,
    blocked.sql_child_number     as blocked_sql_child_number,
    sys_obj.name                 as blocked_table_name,
    dbms_rowid.rowid_create(
        rowid_type    => 1,
        object_number => blocked.row_wait_obj#,
        relative_fno  => blocked.row_wait_file#,
        block_number  => blocked.row_wait_block#,
        row_number    => blocked.row_wait_row#
    )                            as blocked_rowid,
    blockers.wait_id             as blocked_wait_id,
    blockers.wait_event          as blocked_wait_event,
    blockers.wait_event_text     as blocked_wait_event_text,
    blockers.con_id              as data_container_id,
    -- Blocker * Blocked SQL Text
    blocker_sql.sql_text         as blocker_sql_text,
    blocker_prev_sql.sql_text    as blocker_prev_sql_text,
    blocked_sql.sql_text         as blocked_sql_text
from
    v$session_blockers blockers
    inner join
    v$session blocker
        on blocker.sid = blockers.blocker_sid
        and blocker.serial# = blockers.blocker_sess_serial#
    inner join
    v$session blocked
        on blocked.sid = blockers.sid
        and blocked.serial# = blockers.sess_serial#
    inner join
    sys.obj$ sys_obj
        on sys_obj.obj# = blocked.row_wait_obj#
    left outer join
    v$sql blocked_sql
        on blocked_sql.sql_id = blocked.sql_id
        and blocked_sql.child_number = blocked.sql_child_number
    left outer join
    v$sql blocker_sql
        on blocker_sql.sql_id = blocker.sql_id
        and blocker_sql.child_number = blocker.sql_child_number
    left outer join
    v$sql blocker_prev_sql
        on blocker_prev_sql.sql_id = blocker.prev_sql_id
        and blocker_prev_sql.child_number = blocker.prev_child_number
where
    blocked.status = 'ACTIVE';

This query provides detailed information about the sessions involved in a deadlock, including:

• Blocker Session Information: Instance ID, SID, serial number, username, status, machine, program, current and previous SQL IDs and child numbers.
• Blocked Session Information: SID, serial number, username, status, machine, program, blocking session, current SQL ID and child number, blocked table name, row ID being blocked, wait ID, wait event, and wait event text.
• SQL Texts: SQL text for the blocking session's current and previous SQL commands, as well as the blocked session's current SQL command.

Troubleshooting Deadlocks

Once you have identified a deadlock, you can take the following steps to troubleshoot and resolve it:

Step 1: Analyze the Deadlock Graph

The deadlock graph in the trace file provides detailed information about the sessions and the resources involved. Use this information to understand the root cause of the deadlock.

Step 2: Identify the SQL Statements

Review the SQL statements involved in the deadlock. This will help you understand which operations are conflicting.

Step 3: Review Application Logic

Examine the application logic and the sequence of operations. Ensure that transactions acquire locks in a consistent order to avoid cyclic dependencies.
Single-threading related updates and other application changes can often eliminate deadlocks. Additionally, rescheduling batch update jobs to times of low activity can also be beneficial.

Step 4: Implement Locking Strategies

Implement appropriate locking strategies to minimize the chances of deadlocks. Some strategies include:

• Using explicit locks: Use SELECT FOR UPDATE to lock rows explicitly and avoid deadlocks.
• Reducing transaction size: Break large transactions into smaller ones to reduce the locking duration.
• Optimizing query design: Ensure that queries are designed to minimize lock contention.

Step 5: Use Oracle Deadlock Detection and Avoidance Tools

Oracle provides tools and features to help detect and avoid deadlocks, such as:

• Deadlock detection: Oracle automatically detects and resolves deadlocks by rolling back one of the transactions.
• Lock wait timeouts: Set a timeout for lock waits using the SET TRANSACTION command to prevent long waits and potential deadlocks.
• Lock escalation: Oracle can escalate row-level locks to table-level locks to reduce lock contention.

Step 6: Add INITRANS

In certain situations, increasing the INITRANS parameter for target tables and indexes (thereby adding slots to the Interested Transaction List or ITL) can help reducing deadlocks.
INITRANS is a block-level storage parameter that can be specified when creating an object (e.g., a table). It, along with the MAXTRANS parameter, controls concurrent access to the same block. At any given time, a maximum of 255 concurrent sessions can access a block, making 255 the highest possible value for the MAXTRANS parameter.
The value specified for INITRANS determines the initial number of ITLs created in the block. Increasing this value can improve the ability of the database to handle concurrent transactions, thus reducing the likelihood of deadlocks. When creating a table, if INITRANS is set to 25, then 25 different ITL slots will be created in the block's transaction variable header.

Example #1: Resolving a Deadlock

Let's revisit the deadlock scenario and resolve it by changing the application logic.
Modify the transactions to acquire locks in a consistent order:
Session 1:

-- Begin transaction
-- Lock a row in table1
UPDATE table1 SET value = 'X' WHERE id = 1;
-- Lock a row in table2
UPDATE table2 SET value = 'Z' WHERE id = 1;
COMMIT;

Session 2:

-- Begin transaction
-- Lock a row in table1 (acquire locks in the same order as Session 1)
UPDATE table1 SET value = 'W' WHERE id = 1;
-- Lock a row in table2
UPDATE table2 SET value = 'Y' WHERE id = 1;
COMMIT;

Example #2: Resolving a Deadlock

Identify the SID and SERIAL# of the SQL that is blocking other SQL statements. SQL to identify SID and SERIAL# : Method 3: Dynamic Performance Views. Use the following ALTER statement to terminate the session. Note: Do not perform this action on a production database if the SQL statement is executing a critical transaction.

--SYNTAX : alter system kill session 'SID,SERIAL#' immediate;
SQL> alter system kill session '9,27628' immediate;
System altered.

SQL>

Conclusion

Deadlocks in Oracle databases can be challenging to handle, but with the right tools and strategies, you can identify, troubleshoot, and resolve them effectively. By understanding the causes of deadlocks, analyzing deadlock graphs, and implementing proper locking strategies, you can minimize their occurrence and maintain a smooth-running Oracle Database environment.

Related content

• How to identify and troubleshoot deadlocks in the Oracle?
• Oracle AWR Report: Automatic Workload Repository
• SQL AWR Reports
• Oracle Explain Plan | Oracle Execution Plans with DBMS_XPLAN
• Flushing a Bad SQL Plan from Shared Pool
• SQL Tuning in Oracle with DBMS_SQLTUNE
• Using SQLHC for SQL Tuning in Oracle Databases
• Checking Tablespace Usage in Oracle
• Oracle Cost-Based Optimizer and Database Statistics
• How to Check Oracle Database Statistics
• Analyzing Top Wait Events in Oracle
• Identifying Top Current SQL Queries Consuming Time in Oracle
• Identifying Historically Expensive SQL Queries in Oracle
• Retrieving SQL Version Information in Oracle
• Analyzing Wait Events for Specific SQL in Oracle
• Analyzing Wait Events for Specific SQL Between Time Intervals in Oracle
• Identifying SQLs Causing Wait Events Between Snapshots in Oracle
• How to Enable SQL Tracing for User Sessions in Oracle
• How to identify table names for LOB segments in Oracle?

Rate Your Experience

: 90 : 1