Odoo PostgreSQL Lock Contention Runbook (When Writes Stall)

When users report "save button spinning" or workers time out on writes, lock contention is often the hidden cause. This runbook gives operators a deterministic path: confirm contention, identify blockers, and recover without unsafe kill-first behavior.

Incident signals

Treat this as an incident when one or more signals persist for 5–10 minutes:

• Odoo workers timing out on create/write operations.
• psql shows many sessions waiting on locks (wait_event_type = 'Lock').
• API/web requests pile up while CPU is not saturated.
• Long transactions hold locks on high-traffic tables (sale_order, account_move, stock_move).

Step 1 — Capture baseline before changing anything

# Active sessions and lock waits
psql "$ODOO_DB_URI" -c "
select pid, usename, application_name, state, wait_event_type, wait_event,
       now() - query_start as runtime,
       left(query, 160) as query
from pg_stat_activity
where datname = current_database()
order by query_start asc;
"

# Who is blocking whom
psql "$ODOO_DB_URI" -c "
select blocked.pid       as blocked_pid,
       blocked.usename   as blocked_user,
       blocking.pid      as blocking_pid,
       blocking.usename  as blocking_user,
       now() - blocking.query_start as blocking_runtime,
       left(blocking.query, 140) as blocking_query
from pg_stat_activity blocked
join pg_locks blocked_locks on blocked.pid = blocked_locks.pid and not blocked_locks.granted
join pg_locks blocking_locks
  on blocked_locks.locktype = blocking_locks.locktype
 and blocked_locks.database is not distinct from blocking_locks.database
 and blocked_locks.relation is not distinct from blocking_locks.relation
 and blocked_locks.page is not distinct from blocking_locks.page
 and blocked_locks.tuple is not distinct from blocking_locks.tuple
 and blocked_locks.virtualxid is not distinct from blocking_locks.virtualxid
 and blocked_locks.transactionid is not distinct from blocking_locks.transactionid
 and blocked_locks.classid is not distinct from blocking_locks.classid
 and blocked_locks.objid is not distinct from blocking_locks.objid
 and blocked_locks.objsubid is not distinct from blocking_locks.objsubid
 and blocking_locks.granted
join pg_stat_activity blocking on blocking_locks.pid = blocking.pid
where blocked.datname = current_database();
"

Save this output in incident notes. You need evidence for postmortem and prevention.

Step 2 — Verify Odoo-side pressure and narrow blast radius

# Quick service and worker pressure check
odoocli doctor --env production

# Optional: pause heavy scheduled jobs if they are amplifying writes
odoocli cron pause --tag heavy-write

Goal: reduce new write pressure while you clear blockers.

Step 3 — Resolve blockers in safest order

1. Preferred: let near-complete transactions finish if runtime is short and backlog is stabilizing.
2. Next: cancel SQL statement first (keeps session alive).
3. Last resort: terminate backend for clearly stuck/blocking session.

# Safer first move: cancel current statement
psql "$ODOO_DB_URI" -c "select pg_cancel_backend(<blocking_pid>);"

# Escalation: terminate backend only if cancellation fails and impact is widening
psql "$ODOO_DB_URI" -c "select pg_terminate_backend(<blocking_pid>);"

Avoid terminating replication, backup, or migration sessions unless you explicitly validate blast radius.

Step 4 — Confirm recovery trend

# Re-check lock waits every few minutes
psql "$ODOO_DB_URI" -c "
select wait_event_type, count(*)
from pg_stat_activity
where datname = current_database()
group by wait_event_type;
"

# Validate app latency and error trend
odoocli logs tail --service odoo --since 10m --grep "timeout|deadlock|could not obtain lock"

Recovery is real when lock waits decline over consecutive checks and write requests stop timing out.

Step 5 — Exit criteria

• Blocking graph is empty or limited to short-lived expected locks.
• Odoo write paths (order confirm, invoice post, stock validation) succeed end-to-end.
• Error logs show no continuing lock timeout/deadlock burst.
• Any paused heavy cron jobs are resumed in controlled order.

Hardening after incident

• Set lock_timeout/statement_timeout guardrails for risky batch jobs.
• Break large write batches into smaller transactions with checkpoints.
• Add alerting for oldest transaction age and lock-wait session count.
• Rehearse lock-contention recovery in staging with production-like data volume.

The rule: do not optimize for "unlocking fast" at any cost. Optimize for restoring safe write throughput while preserving data integrity.