LOCKS

  #####LOCKS

---

🔍 STEP-BY-STEP: Check and Resolve Locks in PostgreSQL

---

✅ Step 1: List Active Locks

SELECT
    pid,
    usename,
    datname,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    application_name,
    client_addr,
    now() - pg_stat_activity.query_start AS query_age,
    pg_stat_activity.query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted
ORDER BY query_age DESC;

• This shows who is waiting for a lock, and which object is blocked.

---

✅ Step 2: Check Blocked vs Blocking Sessions

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocked_activity.query    AS blocked_query,
    blocking_locks.pid     AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_activity.query   AS blocking_query,
    blocking_activity.state   AS blocking_state,
    now() - blocking_activity.query_start AS blocking_duration
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

• This gives blocking → blocked chains, with full query details and duration.

---

✅ Step 3: Identify the Type of Lock

Common mode values:

• AccessExclusiveLock → BLOCKS all

• RowExclusiveLock, ShareLock, etc. → moderate impact

Use this table:

Lock Mode	Description
AccessExclusiveLock	Full table lock (e.g., DROP, ALTER)
ExclusiveLock	VACUUM FULL, REINDEX
RowExclusiveLock	Insert/update/delete
ShareRowExclusiveLock	CREATE INDEX CONCURRENTLY conflict

---

✅ Step 4: Find and Terminate Blocking Queries

🔍 Option 1: Manually Terminate Blocking PID

SELECT pg_terminate_backend(<blocking_pid>);

Only do this after checking:

• The blocking query is idle or stuck

• It is not a system/critical session

---

✅ Step 5: Monitor Lock Wait Time

To monitor session locks in real-time:

SELECT pid, now() - query_start AS wait_duration, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

---

✅ Step 6: Prevent Future Locks — Good Practices

Tip	Action
🧽 Keep transactions short	Avoid leaving BEGIN sessions open
🛑 No DDL in business hours	Schedule ALTER, DROP off-hours
🧵 Index your foreign keys	Missing indexes cause long share locks
🔁 Avoid large UPDATE/DELETE in one go	Break into batches
💡 Set lock_timeout or statement_timeout	Prevent endless wait

SET lock_timeout = '5s';  -- optional

---

🛠 Example Shell Script to Auto-Detect Blocking Sessions

Let me know if you'd like a shell script that logs blocking chains, so you can add it to cron or monitoring scripts like your pp_health_checks.sh.

---