How to Troubleshoot and Resolve Deadlocks in Oracle Database?

 

Introduction

Deadlocks in Oracle Database occur when two or more sessions block each other, each waiting for a resource the other holds, resulting in an unresolvable conflict. Oracle detects deadlocks automatically and terminates one session with an ORA-00060: deadlock detected error, but identifying the root cause and preventing recurrence is critical for maintaining database performance. This comprehensive tutorial covers the causes of deadlocks, how to detect and troubleshoot them, and effective resolution strategies. With real-life scenarios, practical scripts, and best practices, this guide is designed for beginners and experienced professionals alike to ensure your Oracle Database runs smoothly.

---

What Causes Deadlocks in Oracle Database?

Deadlocks typically arise due to conflicting access to database resources, such as table rows or locks. Common causes include:

1. Concurrent DML Operations: Multiple sessions updating or deleting the same rows in a different order.

2. Uncommitted Transactions: Long-running transactions holding locks, preventing other sessions from proceeding.

3. Poor Application Design: Applications issuing conflicting updates without proper transaction management.

4. Bitmap Index Updates: Frequent updates to tables with bitmap indexes, causing lock contention.

5. Foreign Key Constraints: Unindexed foreign keys leading to table-level locks during updates.

6. Application Logic Errors: Code that locks resources unnecessarily or in inconsistent orders.

Example Deadlock Scenario:

• Session 1 updates row A and waits to update row B.

• Session 2 updates row B and waits to update row A.

• Result: Neither session can proceed, causing a deadlock.

---

Step 1: Detecting Deadlocks

Oracle automatically detects deadlocks and logs details in a trace file, raising an ORA-00060 error for one session. To troubleshoot, start by identifying the deadlock details.

1.1 Check the Alert Log

The database alert log records deadlock occurrences.

Location: Typically in $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/alert_<instance_name>.log.

Sample Alert Log Entry:

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc.

Action: Locate the trace file mentioned in the alert log for detailed deadlock information.

1.2 Analyze the Trace File

The trace file contains the SQL statements, sessions, and resources involved in the deadlock.

Sample Trace File Excerpt:

Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010018-00001234      25     101     X             26     102           X
TX-0002001A-00005678      26     102     X             25     101           X

Session 101: UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
Session 102: UPDATE orders SET status = 'Processed' WHERE order_id = 2;

Observation: Session 101 holds a lock on order_id = 1 and waits for order_id = 2, while Session 102 holds order_id = 2 and waits for order_id = 1, causing a deadlock.

1.3 Query V$SESSION and V$LOCK

Identify active sessions and locks in real time.

Query:

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    l.type,
    l.lmode,
    l.request,
    l.resource_name
FROM 
    v$session s
JOIN 
    v$lock l ON s.sid = l.sid
WHERE 
    l.request > 0
ORDER BY 
    s.sid;

Explanation:

• l.request > 0 indicates a session waiting for a lock.

• l.type = 'TX' denotes a transaction (row-level) lock, common in deadlocks.

Real-Life Scenario: In an e-commerce database, users report intermittent ORA-00060 errors during order processing. The trace file shows two sessions updating the ORDERS table in conflicting orders, confirming a deadlock.

---

Step 2: Reproducing the Deadlock

To understand and resolve the deadlock, reproduce it in a test environment.

Example Setup: Create a test table and simulate a deadlock.

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    status VARCHAR2(20)
);

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
INSERT INTO orders (order_id, status) VALUES (2, 'Pending');
COMMIT;

Simulate Deadlock:

• Session 1:

UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
-- Do not commit

• Session 2:

UPDATE orders SET status = 'Processed' WHERE order_id = 2;
UPDATE orders SET status = 'Processed' WHERE order_id = 1; -- Waits for Session 1

• Session 1 (continued):

UPDATE orders SET status = 'Shipped' WHERE order_id = 2; -- Deadlock occurs

Result: Oracle detects the deadlock and terminates one session with ORA-00060, generating a trace file.

---

Step 3: Resolving Deadlocks

Once identified, resolve deadlocks using these strategies.

3.1 Enforce Consistent Update Order

Ensure sessions update rows in a consistent order to avoid circular waits.

Example Fix: Modify application code to update ORDERS by order_id in ascending order.

Original Code (Causing Deadlock):

-- Session 1
UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
UPDATE orders SET status = 'Shipped' WHERE order_id = 2;

-- Session 2
UPDATE orders SET status = 'Processed' WHERE order_id = 2;
UPDATE orders SET status = 'Processed' WHERE order_id = 1;

Fixed Code:

-- Both sessions update in ascending order_id
UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
UPDATE orders SET status = 'Shipped' WHERE order_id = 2;

Pros:

• Prevents deadlocks without database changes.

• Simple to implement in application logic.

Cons:

• Requires code changes.

• May not be feasible for complex applications.

Real-Life Fix: In the e-commerce system, the application was modified to sort updates by order_id, eliminating deadlocks.

3.2 Reduce Transaction Duration

Shorten transaction durations to release locks faster.

Example: Long-Running Transaction

BEGIN
    UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
    -- Long processing (e.g., 10 seconds)
    DBMS_LOCK.SLEEP(10);
    UPDATE orders SET status = 'Shipped' WHERE order_id = 2;
    COMMIT;
END;
/

Optimized Version: Commit immediately after each update.

BEGIN
    UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
    COMMIT;
    UPDATE orders SET status = 'Shipped' WHERE order_id = 2;
    COMMIT;
END;
/

Pros:

• Reduces lock contention.

• Minimizes deadlock risk.

Cons:

• May increase commit overhead.

• Requires application redesign.

3.3 Index Foreign Keys

Unindexed foreign keys can cause table-level locks, leading to deadlocks.

Example:

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    status VARCHAR2(20),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

Pros:

• Prevents table-level locks during foreign key updates.

• Improves query performance.

Cons:

• Adds storage and maintenance overhead.

Best Practice: Always index foreign key columns to avoid deadlocks.

3.4 Avoid Bitmap Indexes in OLTP

Bitmap indexes can cause deadlocks in high-DML environments due to their locking behavior.

Check for Bitmap Indexes:

SELECT index_name, index_type, table_name 
FROM dba_indexes 
WHERE index_type = 'BITMAP';

Action: Replace bitmap indexes with B-Tree indexes for transactional tables.

DROP INDEX idx_orders_status;
CREATE INDEX idx_orders_status ON orders(status);

Real-Life Fix: The e-commerce database had a bitmap index on the status column. Replacing it with a B-Tree index reduced deadlocks during order updates.

3.5 Use NOWAIT or Timeout Clauses

Instruct sessions to fail immediately or after a timeout instead of waiting indefinitely.

Example:

UPDATE orders SET status = 'Shipped' WHERE order_id = 1 NOWAIT;

Pros:

• Prevents sessions from hanging, reducing deadlock likelihood.

• Allows applications to handle failures gracefully.

Cons:

• Requires application logic to retry or handle errors.

• May lead to more frequent ORA-00054 errors (resource busy).

---

Step 4: Advanced Troubleshooting

For persistent deadlocks, use these advanced techniques:

4.1 Enable Deadlock Tracing

Increase diagnostic detail by enabling deadlock tracing.

Steps:

1. Set the event for detailed deadlock tracing:

ALTER SYSTEM SET events '60 trace name errorstack level 3';

2. Analyze the resulting trace files in $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace.

Pros:

• Provides detailed lock and session information.

• Useful for complex deadlocks.

Cons:

• Generates large trace files.

• Requires expertise to interpret.

Action: Disable tracing after troubleshooting:

ALTER SYSTEM SET events '60 trace name errorstack off';

4.2 Use Resource Manager

Control session concurrency to reduce contention.

Example:

BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'ORDER_PROCESSING_PLAN',
        group_or_subplan => 'ORDER_GROUP',
        max_active_sessions => 10
    );
END;
/

Pros:

• Limits concurrent sessions, reducing deadlock risk.

• Prioritizes critical workloads.

Cons:

• Complex to configure.

• May delay non-critical sessions.

4.3 Analyze Application Logic

Use Oracle’s SQL Tuning Advisor to identify problematic SQL statements.

Example:

BEGIN
    DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => 'abc123',
        task_name => 'TUNE_ORDER_UPDATE'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_ORDER_UPDATE');
    DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_ORDER_UPDATE');
END;
/

Pros:

• Identifies SQL contributing to deadlocks.

• Suggests optimizations like indexing.

Cons:

• Requires Tuning Pack license.

• May require application changes.

---

Step 5: Monitoring and Prevention

Prevent deadlocks with ongoing monitoring and best practices:

1. Monitor Deadlocks:

SELECT 
    sid,
    serial#,
    username,
    sql_id,
    event,
    wait_time
FROM 
    v$session
WHERE 
    event LIKE '%deadlock%';

2. Schedule Regular Checks: Use DBMS_SCHEDULER to monitor V$LOCK and alert on potential deadlocks.

3. Audit Application Code: Review application logic for inconsistent update orders or long transactions.

Example Monitoring Script:

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    q.sql_text
FROM 
    v$session s
JOIN 
    v$lock l ON s.sid = l.sid
JOIN 
    v$sql q ON s.sql_id = q.sql_id
WHERE 
    l.request > 0
    AND l.type = 'TX'
ORDER BY 
    s.sid;

---

Pros and Cons of Deadlock Resolution Techniques

Technique	Pros	Cons
Consistent Update Order	Prevents deadlocks	Requires code changes
Short Transactions	Reduces lock contention	May increase commit overhead
Index Foreign Keys	Avoids table-level locks	Adds storage/maintenance overhead
Replace Bitmap Indexes	Improves OLTP performance	Requires index redesign
NOWAIT Clause	Avoids waiting, reduces deadlocks	Needs error-handling logic

---

Best Practices and Standards

1. Enforce Update Order: Always update rows in a consistent order (e.g., by primary key).

2. Minimize Transaction Scope: Commit frequently to release locks.

3. Index Foreign Keys: Ensure all foreign key columns are indexed.

4. Avoid Bitmap Indexes in OLTP: Use B-Tree indexes for transactional systems.

5. Monitor Proactively: Set up alerts for ORA-00060 errors using Oracle Enterprise Manager or custom scripts.

6. Test Changes: Reproduce deadlocks in a test environment before applying fixes.

---

Real-Life Case Study

Scenario: An e-commerce platform experiences frequent ORA-00060 errors during order processing, disrupting user transactions.

Actions:

1. Analyzed the trace file, identifying two sessions updating the ORDERS table in different orders.

2. Modified application code to update order_id in ascending order:

UPDATE orders SET status = 'Shipped' WHERE order_id = 1;
COMMIT;
UPDATE orders SET status = 'Shipped' WHERE order_id = 2;
COMMIT;

3. Added an index on the customer_id foreign key:

CREATE INDEX idx_orders_customer ON orders(customer_id);

4. Replaced a bitmap index on status with a B-Tree index:

DROP INDEX idx_orders_status;
CREATE INDEX idx_orders_status ON orders(status);

Result: Deadlocks were eliminated, and order processing became reliable.

---

Conclusion

Deadlocks in Oracle Database can disrupt operations, but with systematic troubleshooting—analyzing trace files, enforcing consistent update orders, indexing foreign keys, and optimizing transactions—you can resolve and prevent them. Use the provided scripts to detect deadlocks, apply best practices, and monitor proactively to ensure a stable database environment. By understanding the root causes and implementing targeted solutions, you can keep your Oracle Database running smoothly.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone