Introduction Foreign key deadlocks occur when concurrent transactions insert rows referencing the same parent rows, and the database engine acquires conflicting locks on the parent table during constraint validation. This is common in order processing systems where many order_items reference the same order or customer rows.

Symptoms - PostgreSQL logs `ERROR: deadlock detected` with `Process X waits for ShareLock on transaction Y` - MySQL reports `ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction` - Order creation fails intermittently under load (5-10% failure rate) - Deadlock graph shows circular wait on foreign key index locks

Common Causes - Missing index on the foreign key column, causing table-level scans during constraint checks - Multiple tables with circular foreign key relationships - Batch inserts that reference parent rows in non-sequential order across transactions - Long-running transactions holding locks on parent rows while waiting for child inserts

Step-by-Step Fix 1. **Check for deadlock errors in PostgreSQL logs**: ```sql -- Enable deadlock logging ALTER SYSTEM SET log_lock_waits = on; ALTER SYSTEM SET deadlock_timeout = '1s'; SELECT pg_reload_conf(); ```

  1. 1.Identify missing indexes on foreign key columns:
  2. 2.```sql
  3. 3.-- PostgreSQL: find FK columns without indexes
  4. 4.SELECT
  5. 5.c.conrelid::regclass AS table_name,
  6. 6.a.attname AS column_name,
  7. 7.c.confrelid::regclass AS referenced_table
  8. 8.FROM pg_constraint c
  9. 9.JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
  10. 10.WHERE c.contype = 'f'
  11. 11.AND NOT EXISTS (
  12. 12.SELECT 1 FROM pg_index i
  13. 13.WHERE i.indrelid = c.conrelid
  14. 14.AND c.conkey[1] = ANY(i.indkey)
  15. 15.);
  16. 16.`
  17. 17.Create missing indexes on foreign key columns:
  18. 18.```sql
  19. 19.CREATE INDEX idx_order_items_order_id ON order_items (order_id);
  20. 20.CREATE INDEX idx_order_items_product_id ON order_items (product_id);
  21. 21.CREATE INDEX idx_orders_customer_id ON orders (customer_id);
  22. 22.`
  23. 23.Redesign transaction order to insert parents before children consistently:
  24. 24.```python
  25. 25.def create_order_with_items(db, customer_id, items):
  26. 26.with db.transaction():
  27. 27.# Always insert parent first
  28. 28.order_id = db.execute(
  29. 29."INSERT INTO orders (customer_id, status, created_at) VALUES (%s, 'pending', NOW()) RETURNING id",
  30. 30.(customer_id,)
  31. 31.)
  32. 32.# Then insert children
  33. 33.for item in items:
  34. 34.db.execute(
  35. 35."INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (%s, %s, %s, %s)",
  36. 36.(order_id, item['product_id'], item['quantity'], item['price'])
  37. 37.)
  38. 38.`
  39. 39.Use SELECT ... FOR SHARE to pre-lock parent rows:
  40. 40.```sql
  41. 41.BEGIN;
  42. 42.SELECT id FROM customers WHERE id = 42 FOR SHARE;
  43. 43.INSERT INTO orders (customer_id, total) VALUES (42, 150.00);
  44. 44.COMMIT;
  45. 45.`
  46. 46.Implement retry logic for deadlock failures:
  47. 47.```python
  48. 48.import time

def retry_on_deadlock(func, max_retries=3): for attempt in range(max_retries): try: return func() except Exception as e: if hasattr(e, 'pgcode') and e.pgcode == '40P01': time.sleep(0.1 * (2 ** attempt)) continue raise ```

Prevention - Always index foreign key columns—this is the single most effective prevention - Enforce consistent insert ordering: parent tables first, then child tables - Keep transactions short to reduce lock hold time - Use `NOWAIT` or `SKIP LOCKED` to fail fast instead of deadlocking - Monitor deadlocks with `pg_stat_database.deadlocks` and alert on increases - Consider removing unnecessary foreign keys and enforcing referential integrity in application code for extremely high-throughput systems