Compatible with Cursor, Claude Code, Cline, Roo Code, Antigravity

Database Deadlock & Concurrency Race Condition Diagnosis

Diagnose Postgres/MySQL transaction deadlocks, lock contention, and concurrent update race conditions.

SQLPostgreSQLMySQLConcurrencyTransactionsBackend

v1.0.0 · Updated 2026-08

Skill Objective

Analyze database deadlock graphs and transaction logs to identify lock acquisition ordering discrepancies and implement deterministic concurrency controls.

Execution Workflow Procedure

Ordered steps the coding agent executes when performing this skill.

Reconstruct Deadlock Graph

Parse the database engine deadlock log to map competing transactions, requested locks, and blocked processes.

Identify Resource Ordering Discrepancy

Determine why Transaction A acquired Lock 1 then requested Lock 2, while Transaction B did the inverse.

Implement Deterministic Locking

Enforce consistent primary key ordering in multi-row updates or introduce optimistic locking with version columns.

Agent Safety Guardrails & Anti-Hallucination Rules

DO NOT recommend increasing lock timeouts as a substitute for fixing cyclic locking orders.
DO NOT execute long-running transactions holding exclusive locks during peak traffic.

Expected Output Format

Lock acquisition sequence breakdown and refactored SQL/code.

📊 Lock Sequence Analysis: Step-by-step lock graph breakdown.

🛠️ Corrected Transaction Logic: Refactored code with deterministic resource ordering.

Domain Verification Checks

Deterministic Lock Ordering: Sorts entity IDs before batch update execution.

Optimistic Locking: Uses version columns for non-blocking conflict detection.

SKIP LOCKED Pattern: Implements non-blocking worker queue processing.

Example Agent Invocations

"Diagnose this PostgreSQL deadlock log between our inventory update and order checkout transactions."

"Fix concurrent update race conditions in our multi-seat license assignment query."

Install / Configure This Skill

# Place in .cursorrules or create .cursor/rules/database-deadlock-concurrency-diagnosis.mdc
# Trigger: Whenever you perform database deadlock & concurrency race condition diagnosis

---
name: database-deadlock-concurrency-diagnosis
description: Diagnose database deadlocks and lock contention to establish deterministic resource ordering and concurrency controls.
version: 1.0.0
compatibility: [Cursor, Claude Code, Cline, Roo Code, Antigravity]
---

# Database Deadlock & Concurrency Race Condition Diagnosis

## Objective
Resolve database transaction deadlocks by analyzing lock graphs and establishing deterministic row lock acquisition orders.

## Workflow
1. **Lock Graph Analysis**: Parse deadlock logs to identify competing transactions and locked tuples.
2. **Order Discrepancy**: Identify where concurrent routines access rows in conflicting sequence.
3. **Deterministic Refactoring**: Sort IDs ascending before batch updates or apply optimistic locking with `@Version`.

Complementary Agent Prompt

Need a quick one-time prompt for chat?

Try the Database Deadlock & Concurrency Race Condition Investigator — a lightweight version designed for 1-click copying directly into Claude, ChatGPT, or Cursor chat.

View Prompt