Introduction Oracle ORA-00060 `deadlock detected while waiting for resource` occurs when two or more sessions hold locks that the other sessions need, creating a circular dependency. Oracle automatically detects and resolves the deadlock by rolling back one statement, but the application must handle the error and retry.

Symptoms - `ORA-00060: deadlock detected while waiting for resource` in application logs - Alert log shows `ORA-00060` with trace file reference - Trace file contains a deadlock graph showing the conflicting sessions - Deadlocks occur under concurrent load but not during single-user testing - Application transaction fails with rollback of the current statement

Common Causes - Two sessions updating rows in opposite order (e.g., Session A: row 1 then row 2, Session B: row 2 then row 1) - Foreign key constraints without indexes causing table-level locks - Bitmap index modifications causing row-level lock conflicts - Application logic with inconsistent lock ordering - ITL (Interested Transaction List) shortage causing block-level contention

Step-by-Step Fix 1. **Locate the deadlock trace file": ```sql -- Find the trace file path SELECT value FROM v$parameter WHERE name = 'diagnostic_dest'; -- Trace files are in: <diagnostic_dest>/diag/rdbms/<db_name>/<instance_name>/trace/ -- Look for files named: <instance_name>_ora_<pid>.trc ```

  1. 1.**Analyze the deadlock graph in the trace file":
  2. 2.`
  3. 3.-- In the trace file, look for:
  4. 4.-- Deadlock graph:
  5. 5.-- ---------Blocker(s)-------- ---------Waiter(s)---------
  6. 6.-- Resource Name process session holds waits process session holds waits
  7. 7.-- TX-000a001f-00002b49 42 123 X 56 456 S
  8. 8.-- TX-000b002c-00001a3c 56 456 X 42 123 S
  9. 9.`
  10. 10.**Find unindexed foreign keys":
  11. 11.```sql
  12. 12.-- Check for FK columns without indexes
  13. 13.SELECT
  14. 14.table_name,
  15. 15.constraint_name,
  16. 16.cols.column_name
  17. 17.FROM (
  18. 18.SELECT
  19. 19.a.table_name,
  20. 20.a.constraint_name,
  21. 21.LISTAGG(a.column_name, ',') WITHIN GROUP (ORDER BY a.position) AS column_name
  22. 22.FROM user_cons_columns a
  23. 23.JOIN user_constraints c ON a.constraint_name = c.constraint_name
  24. 24.WHERE c.constraint_type = 'R'
  25. 25.GROUP BY a.table_name, a.constraint_name
  26. 26.) cols
  27. 27.LEFT JOIN (
  28. 28.SELECT
  29. 29.table_name,
  30. 30.LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_position) AS indexed_cols
  31. 31.FROM user_ind_columns
  32. 32.GROUP BY table_name
  33. 33.) idx ON cols.table_name = idx.table_name
  34. 34.WHERE idx.indexed_cols IS NULL
  35. 35.OR INSTR(idx.indexed_cols, cols.column_name) = 0;
  36. 36.`
  37. 37.**Create missing indexes on foreign key columns":
  38. 38.```sql
  39. 39.CREATE INDEX idx_order_items_order_id ON order_items(order_id);
  40. 40.CREATE INDEX idx_order_items_product_id ON order_items(product_id);
  41. 41.`
  42. 42.**Implement consistent lock ordering in the application":
  43. 43.```java
  44. 44.// Always lock rows in a consistent order (e.g., by primary key)
  45. 45.List<Long> ids = orderIds.stream().sorted().collect(Collectors.toList());
  46. 46.for (Long id : ids) {
  47. 47.Statement stmt = conn.prepareStatement(
  48. 48."UPDATE orders SET status = ? WHERE id = ? FOR UPDATE");
  49. 49.stmt.setString(1, "processing");
  50. 50.stmt.setLong(2, id);
  51. 51.stmt.executeUpdate();
  52. 52.}
  53. 53.`

Prevention - Index all foreign key columns to reduce lock escalation - Enforce consistent row access ordering across all application code - Keep transactions short to minimize lock hold time - Use `SELECT ... FOR UPDATE NOWAIT` to fail fast instead of deadlocking - Monitor `v$session_wait` for `enq: TX - row lock contention` events - Implement application-level retry logic for ORA-00060 errors - Review deadlock trace files weekly to identify recurring patterns