How to Detect and Analyze PostgreSQL Lock Contention with the MCP Server

The MCP PostgreSQL Operations server exposes a get_lock_monitoring tool that queries pg_locks and pg_stat_activity to reveal real-time blocking relationships and lock contention patterns.

The call518/mcp-postgresql-ops repository provides a FastMCP-based server that simplifies PostgreSQL administration through dedicated monitoring tools. When diagnosing PostgreSQL lock contention, administrators traditionally stitch together multiple system catalog queries manually. This MCP server automates that process through a single, authenticated endpoint that surfaces blocking PIDs, lock modes, and waiting queries from live database sessions.

How the Lock Monitoring Tool Works

The get_lock_monitoring tool is registered as an MCP endpoint using the @mcp.tool() decorator in src/mcp_postgresql_ops/mcp_main.py.

@mcp.tool()
async def get_lock_monitoring(...):
    ...

The decorator exposes the coroutine to any MCP client, including CLI tools, HTTP APIs, or integrated development environments.

When invoked, the tool constructs and executes a comprehensive SQL statement that joins PostgreSQL's internal lock and activity views. The generic helper execute_query in src/mcp_postgresql_ops/functions.py manages the asyncpg connection lifecycle, executing the query and returning structured data as a list of dictionaries.

Querying Lock Relationships and Blocking Sessions

The core detection logic resides in a single SQL statement that identifies contention by comparing granted versus waiting locks. In src/mcp_postgresql_ops/mcp_main.py, the query performs the following operations:

1. Session Correlation The statement joins pg_locks with pg_stat_activity on process ID to attach session context to every lock:

FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid

2. Blocking Identification A critical LEFT JOIN back to pg_locks identifies which session blocks another. The join matches on lock attributes (type, database, relation, etc.) while filtering for the specific contention pattern:

LEFT JOIN pg_locks bl_l … AND NOT l.granted AND bl_l.granted

This logic surfaces the blocked_by relationship by selecting rows where the current lock is not granted while a counterpart is granted, effectively mapping the dependency chain.

3. Diagnostic Context The query retrieves essential columns including bl.pid AS blocked_by, lock type, lock mode, wait events, and the first 80 characters of the query text (LEFT(a.query, 80) AS query) to identify offending statements without requiring additional queries.

4. Result Ordering Results sort by database name, process ID, lock type, and mode for readability:

ORDER BY a.datname, a.pid, l.locktype, l.mode

Dynamic Filtering and Customization

The tool accepts parameters that dynamically modify the WHERE clause, allowing targeted investigation of specific contention scenarios. In src/mcp_postgresql_ops/mcp_main.py, the function builds parameterized conditions based on arguments:

• Granted status: Filter for l.granted = true/false to see only waiting or held locks
• Session state: Isolate active, idle, or idle-in-transaction states
• Lock mode: Target specific modes like ExclusiveLock or RowShareLock
• Lock type: Focus on relation, transactionid, or other lock types
• Username: Restrict results to specific database users

The query builder constructs the final SQL string with proper parameterization to prevent SQL injection while allowing flexible runtime filtering.

Authentication and Execution Flow

Security is enforced through a static token verifier initialized at server startup. The server requires the MCP_POSTGRESQL_OPS_SECRET environment variable to create the authentication layer.

Authentication mechanism: Only clients presenting a valid bearer token can invoke get_lock_monitoring. The FastMCP framework validates the token before executing the coroutine, ensuring that lock monitoring data remains accessible only to authorized operators.

Execution pipeline:

1. Client invokes the tool via MCP CLI, HTTP API, or programmatic interface
2. FastMCP marshals arguments and validates authentication
3. The tool constructs the parameterized SQL query
4. execute_query opens a short-lived asyncpg connection and runs the statement
5. format_table_data (from src/mcp_postgresql_ops/functions.py) converts rows to a human-readable table format
6. The formatted string returns to the client, suitable for immediate display in terminals or chat interfaces

Practical Usage Examples

Command-Line Interface

Monitor all current locks in the default database:

mcp-postgresql-ops get_lock_monitoring

Isolate exclusive locks held by a specific user:

mcp-postgresql-ops get_lock_monitoring username=app_user mode=ExclusiveLock

Display only locks that are waiting (not granted) to identify immediate contention:

mcp-postgresql-ops get_lock_monitoring granted=false

Programmatic Python Integration

import asyncio
from fastmcp import FastMCP

mcp = FastMCP("mcp-postgresql-ops")

async def analyze_locks():
    result = await mcp.run_tool(
        "get_lock_monitoring",
        database_name="salesdb",
        locktype="relation",
        granted="false"
    )
    print(result)

asyncio.run(analyze_locks())

The run_tool method handles JSON payload construction, token signing, and server communication internally.

HTTP API Access

curl -X POST http://localhost:8000/tools/get_lock_monitoring \
  -H "Authorization: Bearer $MCP_POSTGRESQL_OPS_SECRET" \
  -H "Content-Type: application/json" \
  -d '{
        "database_name": "analytics",
        "granted": "false"
      }'

The response contains the same formatted table text available through the CLI.

Summary

• Real-time visibility: The tool queries live pg_locks and pg_stat_activity views to show current lock states and session information.
• Blocking relationship mapping: A self-join on pg_locks identifies which specific PID blocks another transaction, creating a clear dependency graph.
• Flexible filtering: Dynamic SQL construction supports filtering by granted status, user, lock mode, and lock type to isolate problematic sessions.
• Secure access: Static token authentication via MCP_POSTGRESQL_OPS_SECRET ensures only authorized clients access lock data.
• Multiple interfaces: Available through CLI, Python SDK, and HTTP endpoints for integration into various operational workflows.

Frequently Asked Questions

How does the MCP server identify which process is blocking a waiting lock?

The server executes a SQL query in src/mcp_postgresql_ops/mcp_main.py that performs a LEFT JOIN from pg_locks back to itself on matching lock attributes (database, relation, lock type). The join condition NOT l.granted AND bl_l.granted identifies rows where one session holds a granted lock that conflicts with another session's ungranted (waiting) lock, surfacing the blocking PID as blocked_by.

What PostgreSQL system catalogs does the lock monitoring tool query?

The tool primarily queries pg_locks for lock metadata and pg_stat_activity for session and query information. The SQL statement joins these views on the process ID (pid) to correlate locks with their owning sessions, query text, and application names.

Can I filter the lock monitoring results to show only waiting transactions?

Yes. The get_lock_monitoring tool accepts a granted parameter. Setting granted=false appends WHERE l.granted = false to the generated SQL, returning only locks that are currently waiting and contributing to contention. You can combine this with username or database_name parameters to narrow the scope further.

Is the lock monitoring data returned in a machine-readable format?

While the raw query returns structured data from asyncpg, the MCP tool passes results through format_table_data in src/mcp_postgresql_ops/functions.py, which produces a human-readable table string by default. For programmatic consumption, you can access the underlying query logic directly or modify the formatting function to return JSON instead of formatted text.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}