分析数据库死锁并制定修复方案
依据死锁日志还原事务等待环路,提出锁顺序、索引与重试改进,并给出复现验证步骤。
完整提示词
<context>
A backend engineer or DBA is seeing deadlocks or long lock waits on {{database}}. A deadlock is two or more transactions each holding a lock the other needs; the database kills one. Common causes: the same rows updated in a different order by two code paths; a missing index that turns a targeted update into a scan that locks many rows (or, in MySQL InnoDB, gap and next-key locks over ranges); foreign key checks taking shared locks on parent rows; long transactions that hold locks while calling external services; and upserts racing on unique keys. Retrying hides the symptom; the fix is usually a consistent lock order, smaller and shorter transactions, or the right index, with a bounded retry as the safety net.
</context>
<task>
<deadlock_log>
{{deadlock_log}}
</deadlock_log>
{{#code}}
<code>
{{code}}
</code>
{{/code}}
1. Read the report: for each transaction, the statement it was running, the locks it held and the lock it waited for (table, index, lock mode, and rows or ranges if shown), and which one was chosen as the victim. Draw the cycle in one line (T1 holds A, wants B; T2 holds B, wants A).
2. Map statements to code paths if code is given; otherwise say which code to look for (the statements and tables named).
3. Name the root cause from the evidence: inconsistent ordering, a scan due to a missing or unusable index, gap or next-key locking under the current isolation level, foreign key locks, a lock escalation from a broad update, an upsert race, or long transactions. Say how confident you are and what evidence would confirm it.
4. Propose fixes, best first:
- Lock in a consistent order (for example sort ids before updating many rows, or lock the parent row first with `SELECT ... FOR UPDATE` in every path).
- Make the transaction smaller and shorter: no network calls or user waits inside it, batch large updates.
- Add or fix the index so the statement locks only the rows it changes; show the DDL and how to build it online.
- Change the statement (atomic single-statement update, a proper upsert) or, only if justified, the isolation level for that transaction, with the trade-off stated.
5. Retry policy: retry the whole transaction (not the single statement) on the engine's deadlock or serialisation error code, with a small bounded number of attempts and jittered backoff, and only if the transaction is safe to repeat. Log each retry with a metric.
6. Verification: a reproduction with two sessions running the statements in the conflicting order, deadlock and lock wait metrics before and after, and the settings that log deadlocks and lock waits for future diagnosis (to verify for this engine).
</task>
<constraints>
- Base the diagnosis on the log. If the log is truncated or missing the lock details, say what is missing and how to capture it, and keep conclusions provisional.
- Do not recommend lowering isolation globally or disabling foreign keys to make deadlocks go away.
- Mark engine-specific behaviour you are not sure of to verify for this version.
- Every index or DDL change states its lock impact and how to run it online.
{{> output/uncertainty}}
</constraints>
<output_format>
## What happened
The cycle in one line, then a table: transaction | statement | holds | waits for | victim?
## Root cause
Two to five lines with confidence and evidence.
## Fixes
Numbered, best first, each with code or SQL and its trade-off.
## Retry policy
Code sketch and rules.
## How to verify
Checklist including the two-session reproduction.
## Open questions
Bullets.
</output_format>可替换内容
- {{database}}
- {{deadlock_log}}
- {{#code}}
- {{code}}
- {{/code}}
- {{> output/uncertainty}}
分类与标签
编程开发、数据库死锁、事务锁、索引优化、重试策略、故障排查