菜单
Process use when you need to work with deadlock detection. This skill provides deadlock detection and resolution with comprehensive guidance and automation. Trigger with phrases like "detect deadlocks", "resolve deadlocks", or "prevent deadlocks".
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/jeremylongshore/claude-code-plugins-plus-skills --skill detecting-database-deadlocks命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
Implement user sign-up and sign-in flows with Clerk. Use when building authentication UI, customizing sign-in experience, or implementing OAuth social login. Trigger with phrases like "clerk sign-in", "clerk sign-up", "clerk login flow", "clerk OAuth", "clerk social login".
Implement session management and middleware with Clerk. Use when managing user sessions, configuring route protection, or implementing token refresh and custom JWT templates. Trigger with phrases like "clerk session", "clerk middleware", "clerk route protection", "clerk token", "clerk JWT".
Configure enterprise SSO, role-based access control, and organization management. Use when implementing SSO integration, configuring role-based permissions, or setting up organization-level controls. Trigger with phrases like "clerk SSO", "clerk RBAC", "clerk enterprise", "clerk roles", "clerk permissions", "clerk organizations".
正在显示 SKILL.md
基于 SOC 职业分类
| name | detecting-database-deadlocks |
| description | Process use when you need to work with deadlock detection. This skill provides deadlock detection and resolution with comprehensive guidance and automation. Trigger with phrases like "detect deadlocks", "resolve deadlocks", or "prevent deadlocks". |
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*) |
| version | 1.27.0 |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| license | MIT |
| tags | ["database","detecting-database"] |
| compatibility | Designed for Claude Code, also compatible with Codex and OpenClaw |
Detect, analyze, and prevent database deadlocks in PostgreSQL, MySQL, and MongoDB by examining lock wait graphs, parsing deadlock log entries, identifying the application code paths that cause lock ordering conflicts, and implementing preventive patterns.
pg_locks, INNODB_LOCK_WAITS)psql or mysql CLI for executing diagnostic querieslog_lock_waits = on and deadlock_timeout = 1s configuredinnodb_print_all_deadlocks = ON for deadlock logging to error logCheck for currently blocked transactions and their blockers:
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid JOIN pg_locks bl2 ON bl2.locktype = bl.locktype AND bl2.relation = bl.relation AND bl2.pid != bl.pid JOIN pg_stat_activity blocking ON blocking.pid = bl2.pid WHERE NOT bl.grantedSELECT * FROM information_schema.INNODB_LOCK_WAITSParse recent deadlock events from database logs:
ERROR: deadlock detected entries, which include the two conflicting queries and the lock typesSHOW ENGINE INNODB STATUS\G and examine the LATEST DETECTED DEADLOCK sectionConstruct the lock wait graph from the deadlock log. Map which transaction held which lock and which lock each transaction was waiting for. The circular dependency reveals the deadlock cycle. Identify the specific rows or index ranges involved.
Trace the deadlocking queries back to application code. Use Grep to find the SQL statements in the codebase and identify the transaction boundaries (BEGIN/COMMIT blocks or ORM transaction decorators). Map the full sequence of operations within each transaction.
Identify the root cause pattern:
READ COMMITTED isolation.Implement deadlock prevention strategies:
SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED to fail fast instead of waitingAdd retry logic for deadlock victims. When the database aborts a transaction due to deadlock, catch the error (PostgreSQL error code 40P01, MySQL error code 1213) and retry the entire transaction up to 3 times with a short random delay.
Monitor deadlock frequency over time. Create a query or script that counts deadlock events per hour from the database logs. Alert when deadlock frequency exceeds the baseline by more than 3x.
For persistent deadlocks on specific tables, consider advisory locks (pg_advisory_lock() in PostgreSQL) to serialize access to contended resources at the application level, avoiding database-level lock contention entirely.
Document all identified deadlock patterns, root causes, and fixes in a deadlock analysis report for the development team.
| Error | Cause | Solution |
|---|---|---|
PostgreSQL error 40P01: deadlock detected | Circular lock dependency between transactions | Implement retry logic; fix lock ordering in application code; reduce transaction scope |
MySQL error 1213: Deadlock found when trying to get lock | InnoDB detected circular wait in lock wait graph | Enable innodb_print_all_deadlocks; analyze SHOW ENGINE INNODB STATUS; implement retry logic |
| Lock wait timeout (not deadlock) | Transaction holding lock too long, exceeding lock_wait_timeout | Investigate the blocking transaction; increase timeout or implement NOWAIT; optimize the long-running transaction |
| Phantom deadlocks in monitoring | Transient lock waits resolved before deadlock detection runs | Increase monitoring frequency; use database deadlock log instead of snapshot queries; set deadlock_timeout lower |
| Deadlock frequency increases after schema change | New index or constraint creates additional lock targets | Analyze new lock patterns with EXPLAIN and pg_locks; adjust transaction scope to avoid locking new index entries |
Classic opposite-ordering deadlock in an order processing system: Transaction A processes order 100 (locks order row), then updates inventory for product 50 (waits for inventory lock). Transaction B processes order 200 with product 50 (locks inventory row), then updates order 100 status (waits for order lock). Fix: always lock inventory first, then order, regardless of the business flow.
MySQL gap lock deadlock on a queue table: Two workers concurrently DELETE FROM job_queue WHERE status = 'pending' LIMIT 1. InnoDB gap locks on the index range conflict even though the workers target different rows. Fix: use SELECT ... FOR UPDATE SKIP LOCKED to skip already-locked rows, or add unique job IDs and target specific rows.
Foreign key deadlock between parent and child inserts: Concurrent transactions inserting into order_items (child) acquire shared locks on orders (parent) for FK validation. A third transaction updating orders requires an exclusive lock and deadlocks with the shared FK locks. Fix: explicitly SELECT ... FOR UPDATE on the parent order row before inserting child items.