Track down and prevent database deadlocks

Prompt that reads a deadlock report, finds the conflicting lock order and proposes lasting fixes. Paste it into an AI tool of your choice, which then resolves the deadlock.

Topic: Apps & prototypes
Type: Prompt
Level: Intermediate
Time: about 15 min

Results depend on the tool, the model and what you enter.

Result preview

Example: written by our editors, not real AI output.

Timeline: 1. Job A locks order 17, then wants stock row 5 2. Request B locks stock row 5, then wants order 17 3. Database aborts B Cause: opposite lock order Fix 1: always lock order before stock Fix 2: index on stock.product_id Fix 3: retry B up to 3 times with short random wait

Fill in

What you type stays in this browser window. We store and send nothing.

DatabaseWhich database is used?

PatternWhen does it happen?

ImpactWhat do you see?

Your prompt

Help me understand and prevent a database deadlock.
Database: [Database]
Pattern: [Pattern]
Impact: [Impact]
Approach: 1. Read the deadlock report and name the two or more transactions involved, the rows or index ranges they lock and the order in which they do it. 2. Explain in plain words why each transaction waits for the other. 3. Check whether both code paths touch the same tables in a different order and propose one fixed order for all of them. 4. Look for transactions that stay open too long, such as network calls or user input inside a transaction, and missing indexes that make locks cover more rows than needed. 5. Propose a safe retry for the transaction the database aborts, with a limit and a short random wait. 6. Suggest a test or load script that reproduces the conflict before and after the fix.
Output: timeline of the deadlock, cause in two sentences, list of changes ordered by effect and a reproduction plan.

Take particular care to find the lock order first and fix the cause instead of only retrying.

Before you answer, check: Are all instructions met? If key information is missing or a placeholder in square brackets is still in the text, ask instead of guessing. Mark assumptions as assumptions.

Help me understand and prevent a database deadlock.
Database: [Database]
Pattern: [Pattern]
Impact: [Impact]
Approach: 1. Read the deadlock report and name the two or more transactions involved, the rows or index ranges they lock and the order in which they do it. 2. Explain in plain words why each transaction waits for the other. 3. Check whether both code paths touch the same tables in a different order and propose one fixed order for all of them. 4. Look for transactions that stay open too long, such as network calls or user input inside a transaction, and missing indexes that make locks cover more rows than needed. 5. Propose a safe retry for the transaction the database aborts, with a limit and a short random wait. 6. Suggest a test or load script that reproduces the conflict before and after the fix.
Output: timeline of the deadlock, cause in two sentences, list of changes ordered by effect and a reproduction plan.

Take particular care to find the lock order first and fix the cause instead of only retrying.

Before you answer, check: Are all instructions met? If key information is missing or a placeholder in square brackets is still in the text, ask instead of guessing. Mark assumptions as assumptions.

This is an instruction for an AI, not the result itself.

Where this prompt works

These AI tools are a good fit for this prompt:

How to use it

Fill in the fields or tap a suggestion. Leave out names, addresses and other personal data.
Copy the finished prompt into an AI tool of your choice.
Paste the deadlock report from the database log and the queries of both code paths, with table names kept and customer data removed.

Why does this work?

A deadlock is rarely bad luck. It nearly always comes from two code paths that lock the same things in a different order, and a fixed order removes it for good.

Tips

Test changes to transactions and indexes on a copy of the database before they go live.

You may use our prompts freely, including at work. Please do not republish them as your own collection. We give no warranty for AI results; check them yourself. Terms of use

Track down and prevent database deadlocks

Result preview

Fill in

Your prompt

Where this prompt works

How to use it

Why does this work?

Tips

Also a good fit

Choose pagination for an API list New

Write an API reference people can use right away New

Plan a throttled data backfill New