New: 98,000+ skills indexed from GitHub repos with 100+ stars · every skill safety-checkedBrowse top skillsTop skills
Esc to closeBrowse all skills
Submit a skillBrowse skills
Submit a skillBrowse skills

Agent skill

Detecting Database Deadlocks

by jeremylongshore in jeremylongshore/tons-of-skills-marketplace

Process use when you need to work with deadlock detection. An agent skill from jeremylongshore/tons-of-skills-marketplace.

MITAuto-check passedDatabases

Install Detecting Database Deadlocks

skills CLI
$ npx skills add jeremylongshore/tons-of-skills-marketplace --skill detecting-database-deadlocks -a claude-code

Project install by default; add -g for ~/.claude/skills/.

GitHub CLI
$ gh skill install jeremylongshore/tons-of-skills-marketplace detecting-database-deadlocks --agent claude-code

Project scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).

Manual copy
$ git clone --depth 1 https://github.com/jeremylongshore/tons-of-skills-marketplace.git skills-src && mkdir -p .claude/skills && cp -r skills-src/skills/.curated/detecting-database-deadlocks .claude/skills/detecting-database-deadlocks && rm -rf skills-src

Use ~/.claude/skills/ instead of .claude/skills for a personal install. The folder must contain SKILL.md.

Claude Code skills documentation · loads skills from .claude/skills/

Facts

Skill name
detecting-database-deadlocks
GitHub stars
2.8k
Token cost
~2k tokens
SKILL.md length
931 words
Files
5 (incl. scripts, references, assets)
Skills in repo
3,342
Repo updated
First seen
Licence
MIT

At a glance

Process use when you need to work with deadlock detection. An agent skill from jeremylongshore/tons-of-skills-marketplace.

  • Works in 10 steps: Check for currently blocked transactions… → Parse recent deadlock events from… → Construct the lock wait graph from the… → …
  • You need to work with deadlock detection
  • SKILL.md covers Overview, Prerequisites, Instructions and Output, plus 3 more sections
  • Runs Python scripts from its folder

What it does

Detecting Database Deadlocks is an agent skill from jeremylongshore/tons-of-skills-marketplace. 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".

Its SKILL.md is about 2k tokens, which your agent loads only when the skill is triggered. The skill folder holds 7 other files, including scripts, reference files and assets (for example `assets/README.md`, `references/README.md` and `scripts/README.md`). Compatibility notes: Designed for Claude Code

It sits in Databases. It works with PostgreSQL and MySQL. The repository describes itself as: Model-agnostic agent-skills platform with a harness-free canonical layer, verified adapters, and the ccpi package manager. Explore at tonsofskills.com. The licence is MIT.

When your agent uses it

  • You need to work with deadlock detection
  • With phrases like detect deadlocks
  • Resolve deadlocks
  • Prevent deadlocks

Example prompts

  • “detect deadlocks”
  • “resolve deadlocks”
  • “prevent deadlocks”
  • “/detecting-database-deadlocks”

Requirements

  • Python 3
  • Compatibility (from SKILL.md): Designed for Claude Code
  • Pre-approved tools (allowed-tools): Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)

Workflow steps

10 steps, taken from the first numbered list in SKILL.md.

  1. Check for currently blocked transactions and their blockers
  2. Parse recent deadlock events from database logs
  3. Construct the lock wait graph from the deadlock log. Map which transaction held which lock and which lock each transaction was waiting…
  4. Trace the deadlocking queries back to application code. Use Grep to find the SQL statements in the codebase and identify the transaction…
  5. Identify the root cause pattern
  6. Implement deadlock prevention strategies
  7. Add retry logic for deadlock victims. When the database aborts a transaction due to deadlock, catch the error (PostgreSQL error code…
  8. Monitor deadlock frequency over time. Create a query or script that counts deadlock events per hour from the database logs. Alert when…
  9. For persistent deadlocks on specific tables, consider advisory locks (pg_advisory_lock() in PostgreSQL) to serialize access to contended…
  10. Document all identified deadlock patterns, root causes, and fixes in a deadlock analysis report for the development team.

What it can do on your machine

Read from SKILL.md and the folder at commit 80f86df. It shows what the files ask for, not the result of running them.

  • Tool permissions

    Pre-approves these tools, so the agent can use them without asking each time:

    • Read
    • Write
    • Edit
    • Grep
    • Glob
    • Bash(psql:*)
    • Bash(mysql:*)
    • Bash(mongosh:*)

    From allowed-tools in the SKILL.md frontmatter.

  • Runs code

    Ships 2 files in scripts/ (Python), which the agent can run.

    From the folder's file list and the shell code blocks in SKILL.md.

  • Network

    Links to these hosts (documentation or services it may open):

    • postgresql.org
    • wiki.postgresql.org

    From URLs in SKILL.md, links to its own repository left out.

  • Credentials

    Names no API keys, tokens, secrets or passwords.

    From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

  • Compatibility

    Designed for Claude Code

    From compatibility in the SKILL.md frontmatter.

Context cost

Detecting Database Deadlocks loads about 2k tokens when it runs, and up to ~2k if it reads all its reference files. Until then it costs about 69 tokens; SKILL.md has 931 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it
~69
When it runs · the whole SKILL.md, loaded when a task matches
~2k
With references · SKILL.md plus every file in references/, read only if the agent opens them
~2k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Auto-check passed

The automated check found no risky patterns in SKILL.md.

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); the scripts in this folder are not scanned.

SKILL.md

The full file from jeremylongshore/tons-of-skills-marketplace at commit 80f86df, republished under its MIT licence (© jeremylongshore). 931 words, ~1,972 tokens.

Download SKILL.mdSave it as .claude/skills/detecting-database-deadlocks/SKILL.md (or your agent's skills folder). This skill also uses 4 other files; get the full folder from GitHub.
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:*)
compatibility
Designed for Claude Code
version
1.27.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
license
MIT
tags
database, detecting-database

Database Deadlock Detector

Overview

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.

Prerequisites

  • Database credentials with access to lock monitoring views (pg_locks, INNODB_LOCK_WAITS)
  • psql or mysql CLI for executing diagnostic queries
  • PostgreSQL: log_lock_waits = on and deadlock_timeout = 1s configured
  • MySQL: innodb_print_all_deadlocks = ON for deadlock logging to error log
  • Access to database error logs for deadlock event parsing
  • Application source code access for identifying lock-inducing code paths

Instructions

  1. Check for currently blocked transactions and their blockers:

    • PostgreSQL: 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.granted
    • MySQL: SELECT * FROM information_schema.INNODB_LOCK_WAITS

  2. Parse recent deadlock events from database logs:

    • PostgreSQL: Search logs for ERROR: deadlock detected entries, which include the two conflicting queries and the lock types
    • MySQL: Run SHOW ENGINE INNODB STATUS\G and examine the LATEST DETECTED DEADLOCK section
    • Extract: transaction IDs, queries involved, tables and rows locked, and which transaction was rolled back

  3. Construct 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.

  4. 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.

  5. Identify the root cause pattern:

    • Opposite lock ordering: Transaction A locks row 1 then row 2; Transaction B locks row 2 then row 1. Fix by ensuring consistent lock ordering.
    • Index gap locks (MySQL): UPDATE/DELETE on non-existent rows creates gap locks that conflict. Fix by adding the target row first or using READ COMMITTED isolation.
    • Foreign key lock escalation: INSERT into child table acquires shared lock on parent row, conflicting with UPDATE on parent. Fix by locking parent first explicitly.
    • Implicit lock promotion: SELECT with FOR UPDATE followed by UPDATE promotes shared to exclusive lock. Fix by acquiring the exclusive lock upfront.

  6. Implement deadlock prevention strategies:

    • Enforce consistent lock ordering: always lock tables/rows in alphabetical or ID order within transactions
    • Minimize transaction duration: move non-database operations (API calls, file I/O) outside the transaction
    • Use SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED to fail fast instead of waiting
    • Reduce transaction isolation level from SERIALIZABLE to READ COMMITTED where possible

  7. Add 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.

  8. 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.

  9. 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.

  10. Document all identified deadlock patterns, root causes, and fixes in a deadlock analysis report for the development team.

Show full SKILL.md (370 more words)Show less

Output

  • Lock wait graph visualization showing the circular dependency between transactions
  • Deadlock analysis report with root cause, affected queries, and code paths
  • Code fix recommendations with before/after transaction ordering examples
  • Retry logic implementation for deadlock victim transactions
  • Monitoring queries/scripts for tracking deadlock frequency trends

Error Handling

ErrorCauseSolution
PostgreSQL error 40P01: deadlock detectedCircular lock dependency between transactionsImplement retry logic; fix lock ordering in application code; reduce transaction scope
MySQL error 1213: Deadlock found when trying to get lockInnoDB detected circular wait in lock wait graphEnable 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_timeoutInvestigate the blocking transaction; increase timeout or implement NOWAIT; optimize the long-running transaction
Phantom deadlocks in monitoringTransient lock waits resolved before deadlock detection runsIncrease monitoring frequency; use database deadlock log instead of snapshot queries; set deadlock_timeout lower
Deadlock frequency increases after schema changeNew index or constraint creates additional lock targetsAnalyze new lock patterns with EXPLAIN and pg_locks; adjust transaction scope to avoid locking new index entries

Examples

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.

Resources

© jeremylongshore, MIT. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

SKILL.md and 4 other files (scripts, references, assets) in skills/.curated/detecting-database-deadlocks of jeremylongshore/tons-of-skills-marketplace.

  • SKILL.md
  • assets/README.md
  • references/README.md
  • scripts/README.md
  • scripts/analyze_deadlocks.py

Open the folder on GitHubat commit 80f86df

Compare with similar skills

Detecting Database Deadlocks next to the 5 skills that share the most tags, products or categories with it. Stars are the repository's; “used in” counts other GitHub owners with a copy.

Detecting Database Deadlocks compared with similar skills
SkillStarsUsed inTokensAuto-checkLicenceRepo updated
Detecting Database Deadlocks this skilljeremylongshore/tons-of-skills-marketplace2.8k—~2kAutomated safety check: PassMIT
How To Communicatedatabasus/databasus8.8k—~3.9kAutomated safety check: PassMIT
SQL Database Support for pRESTprest/prest4.6k—~1.6kAutomated safety check: PassMIT
Chdb SQLvemetric/vemetric3951 repos~1.2kAutomated safety check: PassApache-2.0
Database DesignMoizIbnYousaf/ai-agent-skills1.1k1 repos~1.2kAutomated safety check: PassMIT
Sql2erystemsrx/sql_to_ER188—~1.1kAutomated safety check: PassAGPL-3.0

Similar skills

  • How To Communicate

    databasus/databasus

    Communicate clearly in every response, progress update and agent-authored document.

    8.8k GitHub stars~3.9k tokensUpdated 17 days ago
    DatabasesAuto-check passed

  • SQL Database Support for pREST

    prest/prest

    Guides classifying, gap-analyzing and scaffolding support for a new SQL database in pREST, from Postgres-compatible variants to entirely new dialects.

    4.6k GitHub stars~1.6k tokensUpdated yesterday
    DatabasesAuto-check passed

  • Chdb SQL

    vemetric/vemetric

    A skill your agent uses when the user wants to run SQL — especially analytical SQL — on local files (parquet/csv/json), URLs, S3 paths, or remote databases (Postgres, MySQL, MongoDB, ClickHouse…

    395 GitHub starsUsed in 1 repo~1.2k tokens
    DatabasesAuto-check passed

  • Database Design

    MoizIbnYousaf/ai-agent-skills

    Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases.

    1.1k GitHub starsUsed in 1 repo~1.2k tokens
    DatabasesAuto-check passed

  • Sql2er

    ystemsrx/sql_to_ER

    A skill your agent uses when the user wants a Chen-model ER diagram from SQL CREATE TABLE statements or DBML, wants to rearrange or clean up an existing sql2er state, wants a skeleton-only overview…

    188 GitHub stars~1.1k tokensUpdated 9 days ago
    DatabasesAuto-check passed

  • Migration

    gocronx-team/gocron

    Create, review, or verify gocron database migrations across SQLite, MySQL, and PostgreSQL.

    808 GitHub stars~1.1k tokensUpdated 7 days ago
    DatabasesAuto-check passed

More from jeremylongshore/tons-of-skills-marketplace

All 3,342 skills in this repo

  • Performing Security Code Review

    jeremylongshore/tons-of-skills-marketplace

    Execute this skill enables AI assistant to conduct a security-focused code review using the security-agent plugin.

    2.8k GitHub starsUsed in 2 repos~1.3k tokens
    Auto-check: notes

  • Adapting Transfer Learning Models

    jeremylongshore/tons-of-skills-marketplace

    Build this skill automates the adaptation of pre-trained machine learning models using transfer learning techniques.

    2.8k GitHub stars~1.1k tokensUpdated yesterday
    Auto-check passed

  • Agent Context Loader

    jeremylongshore/tons-of-skills-marketplace

    Execute proactive auto-loading: automatically detects and loads agents.md files.

    2.8k GitHub stars~1.1k tokensUpdated yesterday
    Auto-check passed

  • Aggregating Performance Metrics

    jeremylongshore/tons-of-skills-marketplace

    Aggregate and centralize performance metrics from applications, systems, databases, caches, and services.

    2.8k GitHub stars~1.2k tokensUpdated yesterday
    Auto-check passed

  • Analyzing Capacity Planning

    jeremylongshore/tons-of-skills-marketplace

    Execute this skill enables AI assistant to analyze capacity requirements and plan for future growth.

    2.8k GitHub stars~947 tokensUpdated yesterday
    Auto-check passed

  • Analyzing Database Indexes

    jeremylongshore/tons-of-skills-marketplace

    Process use when you need to work with database indexing. An agent skill from jeremylongshore/tons-of-skills-marketplace.

    2.8k GitHub stars~2k tokensUpdated yesterday
    Auto-check passed

Works with

Categories

Questions about Detecting Database Deadlocks

What does Detecting Database Deadlocks do?

Process use when you need to work with deadlock detection. An agent skill from jeremylongshore/tons-of-skills-marketplace. Detecting Database Deadlocks is an agent skill from jeremylongshore/tons-of-skills-marketplace. Process use when you need to work with deadlock detection.

When should I use Detecting Database Deadlocks?

Detecting Database Deadlocks fits situations like: you need to work with deadlock detection; with phrases like detect deadlocks; resolve deadlocks; prevent deadlocks.

How do I install Detecting Database Deadlocks in Claude Code?

Run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill detecting-database-deadlocks -a claude-code`. Or copy the skill folder (skills/.curated/detecting-database-deadlocks in jeremylongshore/tons-of-skills-marketplace) into .claude/skills/detecting-database-deadlocks in your project. Claude Code loads it when a task matches its description.

How do I install Detecting Database Deadlocks in Codex?

Run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill detecting-database-deadlocks -a codex`. Or copy the skill folder (skills/.curated/detecting-database-deadlocks in jeremylongshore/tons-of-skills-marketplace) into .agents/skills/detecting-database-deadlocks in your project. Codex loads it when a task matches its description.

Can I use Detecting Database Deadlocks in Cursor, Gemini CLI or GitHub Copilot?

Cursor, Gemini CLI, GitHub Copilot and OpenCode also load SKILL.md folders. With the skills CLI, run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill detecting-database-deadlocks -a cursor` (or -a gemini-cli, github-copilot or opencode for the others). To copy it by hand, put the folder in .cursor/skills/detecting-database-deadlocks, .gemini/skills/detecting-database-deadlocks, .github/skills/detecting-database-deadlocks and .opencode/skills/detecting-database-deadlocks in your project.

What does Detecting Database Deadlocks need to run?

Going by SKILL.md and its folder, Detecting Database Deadlocks needs Python for the scripts in its folder. Our summary lists: Python 3. Its frontmatter pre-approves these tools: Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*). Compatibility (from SKILL.md): Designed for Claude Code.

Does Detecting Database Deadlocks access the network?

SKILL.md names 2 domains. As links in the text: postgresql.org and wiki.postgresql.org. This is read from the text; nothing was executed.

Is Detecting Database Deadlocks safe to install?

Our automated static check of SKILL.md found no risky patterns, such as piping downloads into a shell, reading credential files or hidden Unicode. It is not a guarantee. The check reads SKILL.md only: the scripts in the folder are not scanned, so read them before running anything.

What licence does Detecting Database Deadlocks use?

Detecting Database Deadlocks is published under the MIT licence (declared in SKILL.md). It allows redistribution, so the full SKILL.md is shown on this page.

How many tokens does Detecting Database Deadlocks use?

About 2k tokens (SKILL.md is roughly 7.9k characters). Agents keep only the skill's name and description in context until a task matches; then they load SKILL.md in full. Its references folder adds about 18 tokens, read only when the agent opens those files.

What are the alternatives to Detecting Database Deadlocks?

Skills that share tags, products or a category with Detecting Database Deadlocks: How To Communicate (databasus/databasus, 8.8k stars), SQL Database Support for pREST (prest/prest, 4.6k stars), Chdb SQL (vemetric/vemetric, 395 stars) and Database Design (MoizIbnYousaf/ai-agent-skills, 1.1k stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Detecting Database Deadlocks?

jeremylongshore (a GitHub user) maintains it in jeremylongshore/tons-of-skills-marketplace, which has 2,825 GitHub stars. The repository holds 3,342 skills in this directory. The repository was last updated on October 9, 2026.

Source: jeremylongshore/tons-of-skills-marketplace on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.