How to identify and troubleshoot deadlocks in Oracle?
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?
Deadlocks in oracle 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 in Oracle
Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions. To identify deadlocks in oracle, 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 in Oracle
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