Deadlocks are one of the few database failures that are completely normal. Two transactions lock rows in opposite orders, each waits for the other, and the engine has to shoot one of them. Both InnoDB and PostgreSQL handle this correctly and automatically, and both leave you a report. What changes when you migrate from MySQL to PostgreSQL is not whether deadlocks happen — your access patterns come with you — but how you find out, what the evidence looks like, and how your retry logic should be shaped.
Having chased deadlocks on both engines, I would summarize the difference this way: InnoDB detects faster and reports in one big text blob; PostgreSQL detects on a timer and reports in structured log lines with live catalog views behind them. Neither is strictly better. Both punish teams that never set up the reporting before the first incident.
Detection: immediate graph search vs a one-second timer
InnoDB detects deadlocks eagerly. When a transaction blocks on a lock, InnoDB walks the wait-for graph immediately (while innodb_deadlock_detect is ON, its default) and, if it finds a cycle, rolls back the victim right away. The victim is chosen roughly by weight: the transaction that has modified the least gets rolled back. Under extreme write concurrency the graph search itself can become costly, which is why the detection toggle exists, but almost nobody should turn it off.
PostgreSQL is lazier by design. A transaction that blocks on a lock simply waits; only after deadlock_timeout (default one second) does it run the deadlock check. If a cycle exists, the checking process is cancelled with SQLSTATE 40P01 and the others proceed. The practical consequence: PostgreSQL deadlocks always cost at least deadlock_timeout of stall before resolution, and short lock waits under one second never pay the detection cost at all. That same timeout drives log_lock_waits, which logs any wait exceeding it — turn that on; it is the single highest-value locking diagnostic in PostgreSQL and it is off by default.
Reading the InnoDB report
The classic MySQL move is SHOW ENGINE INNODB STATUS, which includes a LATEST DETECTED DEADLOCK section: both transactions, the statements they were running, the locks they held and wanted, and which one was rolled back. Two habits matter. First, it only shows the latest deadlock, so a noisy afternoon overwrites the interesting one; set innodb_print_all_deadlocks = ON so every deadlock goes to the error log. Second, the report shows the current statement of each transaction, not the earlier statements that acquired the first locks — the actual cause is often two or three statements back, and you reconstruct it from application code.
-- MySQL: the standard toolkit
SHOW ENGINE INNODB STATUS;
SET GLOBAL innodb_print_all_deadlocks = ON;
-- Count deadlocks over time
SELECT name, count FROM information_schema.innodb_metrics
WHERE name = 'lock_deadlocks';Reading the PostgreSQL report
PostgreSQL writes each deadlock to the server log as an ERROR with a DETAIL section listing every process in the cycle: its PID, the lock it waits for, who holds it, and — critically — the query each process was running. The cancelled client receives SQLSTATE 40P01 with the same detail. As with InnoDB, the displayed queries are the currently blocked statements, not necessarily the ones that took the original locks, so the log tells you where the cycle closed, and the fix usually lives earlier in the transaction.
Two configuration habits make these reports dramatically more useful. Set application_name in every service's connection string and include %a in log_line_prefix, so a deadlock report names the services involved instead of two anonymous PIDs — in a system with a dozen services hitting one database, that alone cuts diagnosis time in half. And leave log_lock_waits on permanently: deadlocks are usually preceded by weeks of near-misses, lock waits that resolved just before a cycle formed, and those warnings tell you which table and which transaction pair to fix before the pager does.
What PostgreSQL adds over MySQL is live, queryable lock state. While a lock pile-up is forming — deadlock or not — pg_locks joined to pg_stat_activity shows exactly who blocks whom, and pg_blocking_pids makes the join trivial:
SELECT waiting.pid AS waiting_pid,
blocking_pids.pid AS blocking_pid,
waiting.state,
left(waiting.query, 80) AS waiting_query,
left(blocking_pids.query, 80) AS blocking_query
FROM pg_stat_activity waiting
JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking_pids ON blocking_pids.pid = b.pid
WHERE cardinality(pg_blocking_pids(waiting.pid)) > 0;This view is worth memorizing, and it matters beyond deadlocks: the same blocking chains explain the DDL pile-ups covered in lock queues during DDL migrations, where a single ALTER TABLE waiting behind a long query can stall an entire application without any deadlock at all.
Fixing it: lock ordering beats tuning
On both engines, the durable fix is the same and it is boring: make every code path acquire locks in the same order. The canonical example is a transfer between two accounts: if one code path locks the sender first and another locks the recipient first, two opposing transfers will eventually deadlock. Locking the lower ID first, always, removes the cycle by construction, and the same principle scales to any pair of tables or batch of rows. Sort the IDs before a multi-row UPDATE, always touch parent before child, and collapse read-then-write sequences into single atomic statements so the lock window shrinks. Two engine-specific notes from the field. On InnoDB, missing indexes cause deadlocks that look like application bugs: a locking read without a useful index locks every scanned index record, so two updates on unrelated rows can collide; adding the right index makes a whole deadlock class vanish. On PostgreSQL, watch for foreign keys and ON CONFLICT paths taking row locks you did not write explicitly, and for lock escalation caused by long transactions holding early locks for seconds while doing unrelated work. Shorter transactions fix more deadlocks than any parameter.
Retry strategy: same idea, different error codes
Deadlock victims must retry, and the retry must replay the entire transaction, not just the failed statement — the earlier statements' work was rolled back with it. On MySQL you catch error 1213 (SQLSTATE 40001); on PostgreSQL you catch SQLSTATE 40P01, and if any code runs at REPEATABLE READ or SERIALIZABLE you should handle 40001 serialization failures in the same retry path. Use a small bounded retry count with jittered backoff. One behavioral difference worth encoding: InnoDB rolls back the whole victim transaction on deadlock, while a PostgreSQL failure leaves the transaction in an aborted state until you ROLLBACK — your driver or pool usually handles it, but hand-rolled retry loops must issue the rollback before retrying.
Monitoring the rate, not just the incident
A single deadlock is noise; a rising deadlock rate is a signal that a deploy changed access patterns. MySQL exposes the counter in innodb_metrics as lock_deadlocks. PostgreSQL counts per database in pg_stat_database.deadlocks — sample it and alert on the derivative, not the absolute number. Pair the counter with log_lock_waits output and with latency history for the transactions doing the retrying, because retries hide in averages: a checkout that deadlocks and succeeds on retry reports success while its p95 quietly doubles. That is a query-history problem, and it is exactly what slow query monitoring with retained history is for.
How MonPG helps once you are on PostgreSQL
MonPG monitors PostgreSQL only, and locking evidence is one of the main reasons it exists. It keeps blocking-chain and lock-wait visibility in the same workflow as query history, tracks pg_stat_database.deadlocks over time so a rate change after a deploy is a chart rather than an archaeology project, and retains the pg_stat_statements history that shows which query families got slower when retries crept in. When the first post-migration deadlock page arrives, the difference between "we have the cycle, the queries, and the trend" and "someone is grepping the log" is the difference between a ten-minute fix and a recurring incident. The PostgreSQL monitoring guide covers how the lock, query, and session signals fit together.