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

> Detect and analyze PostgreSQL lock contention with the MCP server. Discover real-time blocking and lock patterns using the get lock monitoring tool. Optimize your database performance.

- Repository: [JungJungIn/mcp-postgresql-ops](https://github.com/call518/mcp-postgresql-ops)
- Tags: how-to-guide
- Published: 2026-02-26

---

**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`](https://github.com/call518/mcp-postgresql-ops/blob/main/src/mcp_postgresql_ops/mcp_main.py).

```python
@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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```sql
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:

```sql
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:

```sql
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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:

```bash
mcp-postgresql-ops get_lock_monitoring

```

Isolate exclusive locks held by a specific user:

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

```

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

```bash
mcp-postgresql-ops get_lock_monitoring granted=false

```

### Programmatic Python Integration

```python
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

```bash
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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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`](https://github.com/call518/mcp-postgresql-ops/blob/main/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.