Lock Monitoring

Hook: Lock monitoring is the database version of checking the hallway outside a meeting room: the room may be fine, but the line outside tells you who is stuck and why.

Question: What is lock monitoring in SQL, and why does it matter for performance?

Answer: Lock monitoring means checking which sessions currently hold locks, which sessions are waiting, and which query is causing the block. In SQL databases, locks protect data from conflicting changes, but too many or too-long locks can make other queries stall. Good monitoring helps you find the blocker fast, distinguish a normal wait from a deadlock, and fix the real cause instead of guessing.

Interview-Ready Answer: I use lock monitoring to answer three things quickly: who is holding the lock, who is waiting, and what object is involved. In PostgreSQL, for example, I would join pg_locks to pg_stat_activity and look for granted = false or wait_event_type = 'Lock'. That lets me spot blocking chains, set a timeout if needed, and tell whether the problem is a long transaction, a missing index causing a large update, or an actual deadlock.

What it is

Detailed Explanation: A lock is a rule that says, 'this row, table, or object is being used in a way that other sessions must respect.' Lock monitoring is not about changing locks; it is about observing the traffic jam they create. In performance work, this matters because a query can be slow for two very different reasons: it is doing too much work, or it is waiting behind another session.

How it works under the hood

1. A session starts a transaction and runs a statement that needs access to data.
2. The database asks its lock manager for a lock mode, such as a read or write lock. A lock manager is the internal component that decides whether two actions can happen at the same time.
3. If the requested lock is compatible with the locks already held, the database grants it immediately.
4. If the lock is not compatible, the session goes into a wait queue and stops making progress on that statement.
5. Monitoring tools read the current snapshot of lock state, usually by joining the lock view to the active session view. In PostgreSQL, pg_locks shows the locks and pg_stat_activity shows the running sessions and their current wait event.
6. If the wait lasts too long, a timeout can cancel the statement. If two sessions wait on each other in a cycle, the deadlock detector runs and aborts one victim so the system can continue.

What to look for first

• Granted vs waiting: granted = true means the lock is already held; false means the session is stuck waiting.
• Who is the blocker: find the session already holding the incompatible lock.
• What object is hot: table, row, index, or transaction ID. That tells you whether the issue is a single row update, a big table operation, or a transaction that stayed open too long.
• How long it has been waiting: a brief wait may be normal; a long wait usually means a bad transaction pattern.

Timeouts worth knowing

The important detail is that lock_timeout is not the same as statement_timeout. One says 'do not wait too long for another session'; the other says 'do not let the whole query run too long.' Also, deadlock_timeout is not a failure limit by itself; it is the point where the database decides to check for a deadlock.

Performance notes and edge cases

Lock monitoring is usually cheap because it reads metadata about current sessions, not user table data. Think of it as roughly linear in the number of active locks and sessions, not the size of the table. In a busy OLTP app, dozens or even hundreds of active locks can be normal, especially during short bursts of write traffic. The real danger is not a high lock count by itself; it is a long transaction that holds locks for seconds or minutes.

One useful version note: in PostgreSQL 9.6 and later, pg_blocking_pids() and wait_event_type make blocker lookup much easier. Older versions often require a more manual self-join on pg_locks. Across engines the idea is the same, but the view names differ: MySQL uses Performance Schema lock tables, and SQL Server uses DMVs.

Memory rule: do not ask only 'how many locks are there?'; ask 'who is waiting, who is blocking, and for how long?' That is the signal that matters.

Real-World Example: Imagine a checkout service in an e-commerce app. A nightly inventory reconciliation job updates thousands of products in one big transaction. It looks harmless because CPU stays low, but it holds row locks for a long time. During peak traffic, customer checkout requests try to update the same product rows and start waiting behind that job.

What you see in production is not always a crash. More often, p95 latency jumps from 120 ms to 6 seconds, the API logs show statements waiting on locks, and the app pool gets clogged with requests that are not doing useful work. In PostgreSQL, a monitoring query would show one session with an old query_start and many sessions with wait_event_type = 'Lock'. The fix is usually not 'add more servers'; it is to shorten the transaction, batch the job, and make sure the write path uses the right index so it touches fewer rows and holds locks for less time.

What goes wrong: if the team misunderstands the symptom, they may raise the connection pool, which makes the queue look bigger and can amplify the outage. Users see spinning checkout pages, customer support sees duplicate retries, and the database shows a single blocker session that stayed idle in transaction after fetching data and before committing.

Follow-up & Tricky Questions:

• How do you find the blocker? In PostgreSQL, I would use pg_blocking_pids() for a quick answer and confirm it with pg_locks plus pg_stat_activity. That gives the blocker, the waiter, and the SQL text behind each session.
• What is the difference between a lock wait and a deadlock? A lock wait may clear if the blocker finishes; a deadlock is a cycle where each session waits on the other. The database detects the cycle and kills one transaction so the rest can continue.
• Which views do you use in different engines? PostgreSQL uses pg_locks and pg_stat_activity; MySQL uses Performance Schema lock tables; SQL Server uses DMVs like sys.dm_tran_locks and sys.dm_exec_requests. The exact names change, but the mental model is always 'who holds, who waits, what object.'
• How do indexes affect lock problems? A good index can make an update or delete touch far fewer rows, which shortens the time locks are held. It does not remove locking, but it often reduces the time window where other sessions can get stuck.
• When should I use lock_timeout? Use it for user-facing requests when waiting is worse than failing fast, such as web APIs or checkout flows. That way you can retry or return a clear error instead of letting the request sit forever.
• Does every SELECT block writers? No. Normal reads in PostgreSQL use MVCC, so they usually do not block writers; a locking read like SELECT ... FOR UPDATE is different because it intentionally takes row locks.
• Does COMMIT release every lock? Transaction-level locks are released at commit or rollback, but session-level advisory locks can stay until you explicitly release them or the session ends. That is a common gotcha in long-lived connections.
• Is statement_timeout the same as lock timeout? No. statement_timeout limits total runtime; lock_timeout limits how long you are willing to wait for a lock. They solve different problems and are often used together.

Tricky / gotcha questions:

• Are locks always a sign of a problem? No. Some locks are normal and necessary; the problem is long waits, large blocker chains, or frequent contention on hot rows.
• Can a slow query be caused by locks even if CPU is low? Yes. Low CPU with high latency is a classic sign of waiting, not computing, and lock monitoring is how you prove it.
• Is a deadlock just a very long wait? No. A deadlock is a cycle. A long wait can eventually clear; a deadlock cannot clear without intervention, so the database must break the cycle.

Common Mistakes:

• Only checking running queries: Correction: also inspect waiting sessions and blocker chains, because the session doing the blocking may be idle in transaction.
• Thinking 'more locks' always means 'worse performance': Correction: the dangerous part is usually wait time and contention, not the raw lock count.
• Mixing up lock waits with slow scans: Correction: use wait_event_type, lock views, and query plans together so you know whether the delay is contention or execution work.
• Using one giant transaction: Correction: break large updates into smaller commits so locks live for less time and fewer requests pile up behind them.

Memory Hook: Think: Who sits, who waits, who blocks the door? If you can answer those three questions, you have found the lock problem.

Cheat Sheet:

• Lock monitoring = find holders, waiters, and objects.
• In PostgreSQL, start with pg_locks + pg_stat_activity.
• Use granted = false or wait_event_type = 'Lock' to spot waiting sessions.
• lock_timeout controls waiting for a lock; statement_timeout controls total runtime.
• Long transactions and hot rows are the usual cause.
• Fixes are shorter transactions, better indexes, and sane retry logic.

Practice Tasks:

• Open two SQL sessions, lock the same row in session 1, and observe the wait in session 2.
• Write a blocker query that shows waiter PID, blocker PID, and SQL text.
• Set lock_timeout to a small value and test how your app or script behaves when a lock is unavailable.