Locks Collector

​

What It Monitors

• Lock contention issues
• Deadlock situations
• Databases with high locking activity

​

Metrics Exposed

​

pg_locks_count

• datname - Database name
• mode - Lock mode (see Lock Modes section)

pg_locks_count{datname="myapp",mode="accesssharelock"} 15
pg_locks_count{datname="myapp",mode="rowexclusivelock"} 3

​

Lock Modes

1. accesssharelock - Acquired by SELECT queries
2. rowsharelock - Acquired by SELECT FOR UPDATE
3. rowexclusivelock - Acquired by INSERT, UPDATE, DELETE
4. shareupdateexclusivelock - Acquired by VACUUM, CREATE INDEX CONCURRENTLY
5. sharelock - Acquired by CREATE INDEX
6. sharerowexclusivelock - Rare lock mode
7. exclusivelock - Blocks all concurrent access
8. accessexclusivelock - Acquired by DROP TABLE, TRUNCATE, ALTER TABLE
9. sireadlock - Used by serializable transactions

​

SQL Query Used

SELECT 
    pg_database.datname as datname,
    tmp.mode as mode,
    COALESCE(count, 0) as count 
FROM 
    (VALUES 
        ('accesssharelock'), 
        ('rowsharelock'), 
        ('rowexclusivelock'), 
        ('shareupdateexclusivelock'), 
        ('sharelock'), 
        ('sharerowexclusivelock'), 
        ('exclusivelock'), 
        ('accessexclusivelock'), 
        ('sireadlock')
    ) AS tmp(mode)
    CROSS JOIN pg_database 
    LEFT JOIN (
        SELECT 
            database, 
            lower(mode) AS mode, 
            count(*) AS count 
        FROM pg_locks 
        WHERE database IS NOT NULL 
        GROUP BY database, lower(mode)
    ) AS tmp2 ON tmp.mode = tmp2.mode 
    and pg_database.oid = tmp2.database 
ORDER BY 1

​

Configuration

​

Enable/Disable the Collector

# Disable the locks collector
postgres_exporter --no-collector.locks

# Explicitly enable it (default behavior)
postgres_exporter --collector.locks

​

Use Cases

​

Detect Lock Contention

pg_locks_count{mode="accessexclusivelock"} > 0

​

Monitor Normal Lock Activity

sum(pg_locks_count{mode="accesssharelock"}) by (datname)

​

Identify Problematic Databases

topk(5, sum(pg_locks_count) by (datname))

​

Alert on Lock Spikes

rate(pg_locks_count[5m]) > threshold

​

Understanding Lock Patterns

​

Normal Activity

• Many accesssharelock (SELECT queries)
• Moderate rowexclusivelock (INSERT/UPDATE/DELETE)
• Few exclusive locks

​

Potential Issues

• High accessexclusivelock - Table maintenance blocking queries
• Many sharerowexclusivelock - Potential deadlock situations
• Sustained high lock counts - Query optimization needed

​

Permissions Required

• CONNECT privilege on the database
• Read access to the pg_locks system view
• For PostgreSQL 10+: pg_monitor or pg_read_all_stats role
• For PostgreSQL <10: Superuser or specific function access

​

Performance Considerations

• Queries the pg_locks system view
• Uses a CROSS JOIN with all lock modes to ensure complete coverage
• Has minimal performance impact
• Query execution time grows with the number of databases and active locks
• Safe to run frequently (every scrape interval)

​

Related Collectors

• Database Statistics - Tracks deadlock counts
• Long Running Transactions Collector - Identifies transactions holding locks (if enabled)

​

Troubleshooting

1. Check for long-running transactions: SELECT * FROM pg_stat_activity
2. Identify blocking queries: Use pg_blocking_pids() function
3. Review application query patterns
4. Consider using connection pooling to reduce lock duration