SQLSTATE 40P01deadlock_detectedHTTP 500
Postgres error 40P01: deadlock detected
deadlock detected
Two or more transactions each held a lock the other needed. Postgres cancelled one of them to break the cycle.
Common causes
- Transactions that update the same rows in different orders.
- Foreign key checks taking share locks on parent rows while another transaction updates them.
- Long transactions that mix many writes.
How to fix it
- Retry the cancelled transaction.
- Always lock or update rows in a consistent order (for example sorted by id).
- Lock what you need up front with SELECT ... FOR UPDATE in that order, and keep transactions short.
-- update a batch in a stable order so concurrent batches cannot deadlock
update inventory i set qty = qty - o.qty
from (select sku, qty from order_lines where order_id = $1 order by sku) o
where i.sku = o.sku;Through a REST API
PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 500 for this error and returns the SQLSTATE in the code field of the JSON error body.
Questions
What does Postgres error 40P01 mean?
40P01 is deadlock_detected in class 40 (Transaction Rollback). Two or more transactions each held a lock the other needed. Postgres cancelled one of them to break the cycle.
How do I fix 40P01?
Retry the cancelled transaction. Always lock or update rows in a consistent order (for example sorted by id). Lock what you need up front with SELECT ... FOR UPDATE in that order, and keep transactions short.
What HTTP status does a REST API return for 40P01?
PostgREST, the REST layer behind supabase-js, answers 500.
Other class 40 errors
- 40001 could not serialize access due to concurrent update