Db Locking¶
18 cards — 🟢 5 easy | 🟡 8 medium | 🔴 5 hard
🟢 Easy (5)¶
1. What PostgreSQL lock level do INSERT, UPDATE, and DELETE operations acquire?
Show answer
RowExclusiveLock. This lock conflicts with ShareLock and AccessExclusiveLock but allows concurrent row modifications on different rows.2. What operations acquire an AccessExclusiveLock in PostgreSQL and why is it dangerous?
Show answer
ALTER TABLE, DROP TABLE, and TRUNCATE acquire AccessExclusiveLock, which conflicts with every other lock type. This blocks all concurrent reads and writes on the table, potentially causing connection pile-ups if the operation is slow.3. How do you prevent a PostgreSQL query from waiting indefinitely for a lock?
Show answer
SET lock_timeout = '5s'; This causes the query to fail immediately with an error if it cannot acquire the requested lock within the timeout period, instead of blocking indefinitely.4. What is the difference between row-level and table-level locks in PostgreSQL?
Show answer
Row-level locks (e.g., from SELECT FOR UPDATE, UPDATE, DELETE) lock individual rows, allowing concurrent access to other rows in the same table. Table-level locks (e.g., AccessExclusiveLock from ALTER TABLE) lock the entire table, blocking all concurrent operations. Row-level locks scale better for concurrent workloads.5. What is a ShareLock and when does PostgreSQL acquire one?
Show answer
ShareLock is acquired by CREATE INDEX (non-concurrent). It allows concurrent reads but blocks writes (INSERT, UPDATE, DELETE). This is why CREATE INDEX on a large table can cause write outages. Use CREATE INDEX CONCURRENTLY instead, which takes a weaker lock and allows writes to continue.🟡 Medium (8)¶
1. How do you find which queries are blocked by locks in PostgreSQL?
Show answer
Join pg_locks with pg_stat_activity: query pg_locks for rows WHERE NOT granted to find blocked locks, then join to pg_stat_activity on pid to see the blocked and blocking queries, users, and durations.2. How does PostgreSQL detect and resolve deadlocks?
Show answer
PostgreSQL runs a deadlock detector every deadlock_timeout interval (default 1 second). When it detects a cycle of lock dependencies, it terminates the youngest transaction in the cycle and returns a deadlock_detected error to that client.3. What is the difference between optimistic and pessimistic locking?
Show answer
Pessimistic locking acquires locks before modifying data (SELECT FOR UPDATE), preventing conflicts upfront — best for high-contention scenarios. Optimistic locking checks a version or timestamp at commit time and retries on conflict — best for low-contention scenarios with longer transactions.4. What does SELECT FOR UPDATE do and when would you use it?
Show answer
It acquires a row-level lock (RowShareLock) on the selected rows, preventing other transactions from modifying or locking them until the current transaction completes. Use it for pessimistic locking patterns like inventory decrement where you need to read-then-write atomically.5. What are practical strategies to prevent deadlocks in application code?
Show answer
Always acquire locks in a consistent order across all transactions (e.g., sort rows by primary key before locking). Keep transactions short to reduce the window for conflicts. Use lock_timeout to fail fast instead of waiting indefinitely. Retry deadlocked transactions with exponential backoff in application code.6. What is lock escalation and does PostgreSQL do it?
Show answer
Lock escalation is when a database engine automatically converts many fine-grained locks (row-level) into a coarser lock (table-level) to reduce memory overhead. PostgreSQL does NOT escalate locks — it maintains row-level locks regardless of count. SQL Server and some other engines do escalate, which can cause unexpected blocking.7. What are advisory locks in PostgreSQL and when would you use them?
Show answer
Advisory locks are application-defined locks that PostgreSQL manages but does not enforce on any table or row. Acquire with pg_advisory_lock(key) or pg_try_advisory_lock(key). Use them for application-level coordination like ensuring only one worker processes a job, rate limiting, or distributed mutex patterns. They must be explicitly released.8. How do you use pg_stat_activity and pg_locks together to diagnose lock waits?
Show answer
Join pg_stat_activity (for query text and duration) with pg_locks (for lock details): SELECT blocked.pid, blocked.query, blocking.pid AS blocker_pid, blocking.query AS blocker_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid JOIN pg_locks bk ON bk.relation = bl.relation AND bk.granted AND bl.pid != bk.pid JOIN pg_stat_activity blocking ON blocking.pid = bk.pid WHERE NOT bl.granted;🔴 Hard (5)¶
1. Why should you set lock_timeout before running ALTER TABLE in production, and what happens if it times out?
Show answer
ALTER TABLE acquires AccessExclusiveLock, which blocks all reads and writes. If the table has active queries, the ALTER waits for them, and new queries queue behind it — creating a cascading pile-up. Setting lock_timeout = '3s' causes the ALTER to fail fast if it cannot get the lock, avoiding the pile-up. On timeout, the ALTER is aborted and no schema change is made.2. What do NOWAIT and SKIP LOCKED do with SELECT FOR UPDATE, and what are their use cases?
Show answer
NOWAIT causes the query to fail immediately if any selected row is already locked (instead of waiting). SKIP LOCKED silently skips rows that are locked by other transactions. SKIP LOCKED is ideal for work-queue patterns where multiple workers pull jobs from the same table without blocking each other.3. How does MySQL InnoDB lock wait behavior differ from PostgreSQL?
Show answer
MySQL InnoDB uses innodb_lock_wait_timeout (default 50 seconds) compared to PostgreSQL lock_timeout (not set by default). MySQL InnoDB also uses gap locks at REPEATABLE READ isolation to prevent phantom reads, which can cause unexpected lock conflicts on ranges of rows that PostgreSQL avoids through its MVCC implementation.4. How do long-running transactions cause lock pile-ups in production?
Show answer
A long-running transaction holds its locks for the entire duration. If an ALTER TABLE then arrives, it requests AccessExclusiveLock and queues behind the long transaction. All subsequent queries on that table queue behind the ALTER, creating a cascading pile-up. Even read queries are blocked because they cannot acquire AccessShareLock while AccessExclusiveLock is pending.5. How do you implement optimistic locking with a version column?