Resolve Amazon Aurora PostgreSQL lock contention with Database Insights: Part 2

# What this piece covers

# Why lock analysis matters Row lock contention can collapse throughput even when CPU and I/O look healthy. Traditional approaches — checking pg_stat_activity or log_lock_waits — are reactive or require constant polling. Database Insights automates collection of lock data and presents it in ways that make blocking chains and their impact on database load obvious.

# Insights captures contention Insights detects contention, it queries pg_stat_activity and pg_locks and calls pg_blocking_pids() to capture blocked and blocking session details. To use these Lock Analysis features with Aurora PostgreSQL or RDS PostgreSQL you must enable Advanced mode in Database Insights.

# The Lock Tree: what to look for The Lock Tree is a hierarchical view of blocking relationships. It shows:

• which sessions are blocking others and how many are blocked by each blocker,
• the last SQL executed by each session,
• wait events and blocking time for each session,
• multi-level blocking chains where a session can be both blocked and blocking others.

You can enable additional columns to surface the specific table, transaction, or row resource under contention.

# Practical steps to resolve contention

• Identify persistent blocking SQL using the Database Load chart sliced by Blocking SQL and the Top SQL tab. Prioritize blockers with the largest contribution to load.
• Terminate or cancel offending sessions when appropriate to free up throughput.

• Adjust lock-related timeout parameters to avoid long-lived waits that cascade into larger bottlenecks.

• Use SKIP LOCKED for queue-style processing so competing sessions skip locked rows instead of waiting.
• Apply optimistic concurrency control (OCC) or retry logic to handle transient conflicts without long locks.
• Consider asynchronous processing for work that can be performed outside the main transaction path.
• Use row splitting to reduce contention on a single hot row by partitioning frequently updated counters or aggregates across multiple rows.

# Reproducing and testing the fixes The post uses a reproducible order-placement workload. The sample repository contains the pgsql-db-setup.sql schema and simulate_avg_load.sh and simulate_contentious_load.sh scripts to recreate the contention scenario. Use an EC2 client with psql to connect to your Aurora cluster and validate fixes in a controlled environment before changing production settings.

# When to use each fix

• Kill blockers: emergency response for immediate throughput restoration.
• Timeouts: reduce the chance of long waits but can surface application exceptions you must handle.
• SKIP LOCKED and asynchronous processing: suitable for queue/worker patterns.
• OCC and row splitting: better for high-concurrency updates to the same logical resource.

# Bottom line Database Insights gives a unified, historical, and real-time view into lock contention. Use the Lock Tree to find blocking relationships, apply short-term remedies to restore throughput, and adopt architectural patterns to prevent recurring contention.