What is a Deadlock?

A deadlock occurs when two or more transactions are waiting for each other to release locks, and neither can proceed. Oracle automatically detects deadlocks and kills one transaction, but that’s a last resort.

Example of a Deadlock:

• Tx1: locks Row A, then tries to lock Row B (but it’s already locked by Tx2)
• Tx2: locks Row B, then tries to lock Row A

They’re both stuck — deadlock.

Best Practices to Avoid Deadlocks in Oracle

1. Always Access Tables and Rows in the Same Order

If your transactions touch multiple tables or rows, ensure that all processes follow the same locking order.

Transaction 1: LOCK TABLE departments IN EXCLUSIVE MODE; UPDATE departments ...; LOCK TABLE employees IN EXCLUSIVE MODE; UPDATE employees ...; COMMIT;

Transaction 2: LOCK TABLE departments IN EXCLUSIVE MODE; UPDATE departments ...; LOCK TABLE employees IN EXCLUSIVE MODE; UPDATE employees ...; COMMIT;

2. Keep Transactions Short and Focused

Keep your BEGIN...COMMIT blocks tight:

• Do not include user input delays or long loops inside transactions
• Avoid heavy computation within a transaction

@Transactional
public void updateAccounts() {
    // do DB updates here
    // avoid sleep() or long business logic
}

3. Use Appropriate Isolation Level

Oracle uses read-consistent architecture, but too high an isolation level (like SERIALIZABLE) increases the risk of deadlocks.

Use READ COMMITTED (default in Oracle) unless your logic truly needs more strict isolation.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. Avoid Select For Update Unless Needed

SELECT ... FOR UPDATE is useful but dangerous if misused — it locks rows even during reads.

-- Use only when truly needed
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

If two sessions do this on different rows, and then try to update each other’s rows — deadlock.

5. Retry Logic in Application Code

Implement retry logic for ORA-00060: deadlock detected errors.

In Spring Boot:

@Retryable(
    value = {DeadlockLoserDataAccessException.class},
    maxAttempts = 3,
    backoff = @Backoff(delay = 500)
)
@Transactional
public void updateCriticalData() {
    // update logic
}

You can also catch and handle SQLException with errorCode == 60.

6. Index Foreign Keys

Unindexed foreign keys are a common cause of deadlocks. When you delete from a parent table, Oracle may lock the child table if the FK isn’t indexed.

Always index foreign key columns!

CREATE INDEX idx_fk_customer_id ON orders(customer_id);

7. Avoid Manual Locks (if possible)

Don’t use DBMS_LOCK, LOCK TABLE, or custom locking unless absolutely necessary. These can force serialization and increase chances of deadlock.

8. Monitor with Deadlock Trace File

Oracle generates trace files for each deadlock. Check:

SHOW PARAMETER user_dump_dest;
-- Check the trace file for "deadlock detected"

Use it to analyze and fix the locking sequence.