Job sql-deadlock-diagnosed-and-removed-for-one-workload · revised 11 October 2026
Find why two parts of your app deadlock, and make that workload run clean
One workload that fails with a database deadlock or lock wait timeout is reproduced on staging, its lock order or transaction scope corrected, and a concurrent test shows it clean.
You might be seeing
- A deadlock error appears in the application or database log, and one of the two requests fails
- Lock wait timeout errors appear when two jobs or users act on the same records at once
- The failure only happens under concurrency and cannot be reproduced by hand
No passwords, keys, card details or admin invites needed to start.
What usually happened
Two or more transactions each hold a lock the others need, usually because they touch the same rows or tables in different orders, or because a transaction stays open while it waits on something slow. The database aborts one of them, which fails a user action or a job. Because it needs concurrency, it is hard to reproduce; adding retries hides the symptom while the cause remains and the retries add load.
Who it’s for: An engineering lead whose application logs deadlock errors or lock wait timeouts under load, so some user actions or jobs fail and are retried.
Usually starts when: The log shows "deadlock detected" or a deadlock rollback, or requests fail with lock wait timeouts, at a rate that has started to cost orders, jobs or support time.
The result: The named workload runs under an agreed concurrent test on a staging copy with no deadlock and no lock wait above your limit, over a run count tied to how often the original code failed in the same test: at least 200 runs and at least ten times the runs per failure seen on the original code. You receive the cycle diagnosed from your own database log and the code change that removes it.
Check whether this job fits
These questions check that there is a deadlock to diagnose and a way to reproduce it. No access is needed.
Checks you can run yourself
See which sessions are blocked right now (PostgreSQL)
During a slowdown, ask your engineer to run this read-only query. It lists waiting sessions and the sessions blocking them. It does not change anything.
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;Look for: Whether one session sits idle in a transaction and blocks many others, or a chain of waiting sessions stays visible for more than a few seconds; both point at a transaction held open, not a deadlock. PostgreSQL checks for a deadlock once a lock wait has lasted deadlock_timeout (one second by default) and aborts one transaction if it finds a cycle, so a real cycle does not stay in this list; it is read from the server log. Send the pattern, not the query text.
What you get
- A diagnosis note drawing the cycle: which statement holds which lock and which waits for it
- A pull request with the code or SQL change, and the concurrent test script
- Test results on the original code (showing the deadlock and how many runs it took per failure) and on the changed code (clean over the run count that follows from it)
- Timeout and logging settings for your engineer to review, such as lock-wait logging, with the reasons
Included
- One workload, meaning one pair or small set of code paths that deadlock, on one PostgreSQL or MySQL database
- Read the database's own deadlock record and map each transaction's statements and the order in which it takes locks
- Reproduce the deadlock on staging with a concurrent test harness before changing anything
- Remove the cycle by consistent lock order, narrower transactions or a supporting index, and add bounded retry only for what remains
Not included
- Applying changes to production or changing server-wide settings yourself
- A single abandoned session that holds locks for hours; that is a timeout setting and operational fix
- Data-model redesign, sharding or changing database engine
- More than one workload
- Deadlocks inside a third-party application whose code we cannot change
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
On the original code the concurrent test on staging reproduces the deadlock or lock-wait failure at least five times within 2,000 runs, and the runs per failure are recorded, so a later pass is meaningful.
Evidence: The run log from the original code showing the engine's deadlock or timeout record, the failure count and the runs per failure.
On the changed code the same concurrent test, with retries disabled, is run for at least 200 runs and at least ten times the runs per failure recorded on the original code (never more than 4,000 under this scope), and produces no deadlock and no lock wait above your limit.
Evidence: The run log with the run count and how it follows from the recorded baseline, the lock-wait record and the harness settings.
The diagnosis note names both transaction paths, the statements, the tables and indexes involved and the order in which each takes its locks.
Evidence: The note, which you can compare with your own deadlock record.
Your existing tests for the affected code pass after the change, and throughput in the concurrent test is reported before and after.
Evidence: Test output and the throughput comparison.
Sign-off. You read the diagnosis and the two run logs, repeat the harness if you wish, then sign off in writing and merge. Payment follows sign-off.
If it fails. If the changed code still deadlocks in the agreed test, you do not pay for this fixed scope and you keep the diagnosis. If the deadlock cannot be reproduced, we stop and explain what we found.
When it fits, and when we stop
It fits when
- PostgreSQL or MySQL (InnoDB), with a deadlock record available or enabled for the next occurrence
- The code paths involved are in a repository we are given access to after agreement
- The workload can run on staging with synthetic data and two or more parallel callers
- The failure recurs often enough to confirm a fix, at least a few times a week or reproducible on demand
We stop and tell you if
- The deadlock cannot be reproduced on staging with the concurrency the log implies
- The original code fails fewer than five times in 2,000 runs of the concurrent test, so a clean run on the fix would prove little
- The cycle involves a third-party tool or ORM internals we cannot change
- Locks are taken by explicit table locks from an external tool outside your code
- There is no deadlock record and it cannot be enabled before the work
What could go wrong
The change is a pull request in your repository. Closing it before merge leaves the code unchanged; after merge, reverting the commit restores the previous behaviour. Any index we add has a script that drops it. No production data or setting is changed by us.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| The fix removes the deadlock by serialising work and slows throughput. | The concurrent test also records throughput before and after, and we report any loss. |
| A retry wrapper hides a remaining cycle. | Acceptance requires zero deadlocks in the test with retries disabled; a retry may be added only for what remains in production and is logged. |
| Staging cannot reproduce the production timing. | We stop if the deadlock cannot be reproduced rather than ship a guess. |
A second reviewer checks that the reproduction on the original code is genuine, that the fix does not simply serialise the work behind one big lock, and that any retry is bounded and logged. Your engineer reviews and merges.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the workload, the engine, the concurrency profile and the lock-wait limit in writing
- Read the deadlock record and draw the cycle: each transaction's statements, locks held and lock awaited
- Reproduce the deadlock on staging with a concurrent harness: run the original code until it has failed at least five times or 2,000 runs have passed, and record the runs per failure
- Remove the cycle with the least invasive change: consistent order, narrower transaction or a supporting index
- Run the same harness against the changed code for at least 200 runs and at least ten times the original code's runs per failure, and compare with the original run
- Independent review of the diagnosis, diff and runs, then hand over the pull request and notes
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access and any permissions are in place. Hosting and database charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
Discuss a monthly database health review if you want lock waits and long transactions watched after this fix.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- PostgreSQL's locking chapter explains how deadlocks are detected, that one transaction is aborted, and that taking locks in a consistent order is the main defence. www.postgresql.org
- MySQL's InnoDB deadlock page describes reading the latest deadlock, logging every one, keeping transactions small and retrying a rolled-back transaction. dev.mysql.com
Questions
Why not just add a retry?
A retry hides the failure and adds load. We remove the cycle first; a bounded, logged retry may remain only for what cannot be removed.
What if I only have lock wait timeouts, no deadlock?
It fits if a pair of transactions blocking each other can be identified and reproduced. A single session left open for hours is a timeout-setting question, not this job.
Do you need to see production?
No. We work from the deadlock record with values removed and reproduce on staging with synthetic data.
How do you know a clean test means the fix works?
A deadlock that fails one run in 50 shows up in a few hundred runs; one that fails one in 400 needs thousands. We first measure how often the original code fails in the same test, then require the fix to run clean for at least ten times that many runs. A workload that fails too rarely to measure that way is quoted separately.
Send an enquiry
Send us
- The engine and version, how often the deadlock happens, and what the two actions do in plain words
- The deadlock record for one occurrence with literal values removed: the statements and tables only
- Whether it fails the user action or is retried by your code
- Do not send credentials, repository access or real rows in the first enquiry
Later, once you agree
- The code paths and schema through an authorised company-controlled repository route
- A staging database with synthetic or anonymised data of realistic size
- The concurrency profile to simulate and the lock-wait limit
- Permission to enable deadlock and lock-wait logging on staging
You own the database, the code and every credential. We work on a copy you prepare, such as a staging database restored from a backup with personal data removed or replaced, and we hand work back as a pull request or a script. We never ask for production passwords, and we do not connect to your production database. You apply any change to production yourself, after a restore point that you have tested.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “sql-deadlock-diagnosed-and-removed-for-one-workload” as the subject.