← Cheat sheet
🔒 Locking labHow SQL Server and PostgreSQL lock, block and deadlock

⚖️ SQL Server vs PostgreSQL, side by side

The same questions, answered for each engine. Use the engine switch above to drill into one.

QuestionSQL Server (incl. RDS)PostgreSQL (incl. Aurora PG)
Do readers block writers by default?Yes under the default locking READ COMMITTED (S locks). No once READ_COMMITTED_SNAPSHOT is ON (RCSI) or with SNAPSHOT.No. MVCC: a plain SELECT takes only ACCESS SHARE on the table and never row locks.
Do writers block writers on the same row?Yes, in every isolation level (U/X key locks).Yes. The second writer waits on the first transaction's ID (Lock:transactionid).
Where do row locks live?In the lock manager memory: one lock per row/key (plus intent locks on page and table).On disk, in the tuple header (xmax + infomask bits). Not in pg_locks and not counted against shared lock memory.
Lock escalation?Yes. About 5,000 locks by one statement on one object → table (or partition) lock.Never. Instead, the shared lock table is sized by max_locks_per_transaction.
Version storetempdb (or the user DB's persistent version store when ADR is on, SQL Server 2019+). Used only when RCSI / SNAPSHOT / triggers / online index ops need it.Old row versions stay in the table itself until VACUUM removes them (bloat if long transactions hold them).
Schema / DDL lockSch-M (conflicts with everything, including NOLOCK readers' Sch-S).ACCESS EXCLUSIVE (conflicts with everything, including plain SELECT).
A waiting DDL blocks new readers?Yes: a queued Sch-M blocks new incompatible requests. Use WAIT_AT_LOW_PRIORITY for online index rebuild / partition switch.Yes: a queued ACCESS EXCLUSIVE blocks every new SELECT. Use SET lock_timeout before DDL and retry.
Lock wait timeout defaultSET LOCK_TIMEOUT -1 (wait forever). Error 1222 when it fires.lock_timeout = 0 (wait forever). SQLSTATE 55P03 when it fires.
Deadlock detectionLock monitor thread every 5 s (more often after it finds one). Victim = lowest DEADLOCK_PRIORITY, then cheapest to roll back. Error 1205.A backend that has waited deadlock_timeout (1 s) checks for a cycle; usually that backend is aborted. SQLSTATE 40P01.
Skip locked rows (queues)WITH (UPDLOCK, READPAST, ROWLOCK)FOR UPDATE SKIP LOCKED (or NOWAIT to error instead)
Application / advisory lockssp_getapplock / sp_releaseapplockpg_advisory_lock, pg_advisory_xact_lock, pg_try_advisory_lock
"Who blocks whom" viewsys.dm_exec_requests.blocking_session_id, sys.dm_tran_lockspg_blocking_pids(pid), pg_locks + pg_stat_activity
Dirty reads possible?Yes with READ UNCOMMITTED / NOLOCK.Never. READ UNCOMMITTED behaves as READ COMMITTED.
Kill a blockerKILL <spid> (rolls back its open transaction, can take a while).pg_cancel_backend(pid) (query) or pg_terminate_backend(pid) (whole session).

🧩 Lock compatibility

Rows = the mode a session requests; columns = the mode another session already holds on the same resource. Tap or hover a cell to see why; tap a mode name for what takes it.

SQL Server · core lock modes
Pick a cell, e.g. requested IX vs held S.

Will these two statements block? (table level)

Key-range locks (SERIALIZABLE only)

Taken on index keys to stop phantoms. Format is Range-Key: RangeS-S = shared range, shared key. RangeI-N is an insert probing a range.

Pick a cell.

Also exist but rarer: IU, SIU, UIX (intent-update combos) and BU (bulk update with TABLOCK). Wait types in sys.dm_os_wait_stats are named after the requested mode: LCK_M_S, LCK_M_U, LCK_M_X, LCK_M_IX, LCK_M_SCH_M, LCK_M_RS_S…

PostgreSQL · table-level lock modes
Pick a cell or a mode. Every lock here shows up in pg_locks.mode with names like AccessShareLock.

Will these two statements block?

Row-level lock modes

Stored in the tuple, not in pg_locks. A waiter shows up as waiting on the holder's transactionid (and a tuple lock).

Pick a cell.

▶️ Two-session simulator

Pick a scenario, then step through it. Each step shows what each session ran, who is waiting on whom, and the locks you would see in the DMVs / pg_locks at that moment. Keys: ← → to step.

🧪 Isolation levels and what each prevents

✓ = prevented, ✗ = can happen.

SQL Server
LevelDirty readNon-repeatable readPhantomHow it works
READ UNCOMMITTED
= WITH (NOLOCK)
✗✗✗No S locks on reads (still Sch-S). Can also miss or double-read rows during page splits / allocation-order scans.
READ COMMITTED
default, locking
✓✗✗S lock per row/page, released as soon as it is read. Readers wait for writers (LCK_M_S).
READ COMMITTED SNAPSHOT (RCSI)✓✗✗Same level name, but each statement reads the last committed version from the version store. Readers don't block writers; writers still block writers.
REPEATABLE READ✓✓✗S locks held until the transaction ends. New rows matching your WHERE can still appear.
SNAPSHOT✓✓✓Transaction-level versioning (needs ALLOW_SNAPSHOT_ISOLATION ON). Updating a row someone else changed since your snapshot → error 3960, transaction rolled back. Write skew still possible.
SERIALIZABLE✓✓✓S locks held to the end plus key-range locks. Most blocking and deadlocks.
RDS SQL Server: the default is locking READ COMMITTED, same as on-prem (Azure SQL Database defaults to RCSI). You can enable RCSI with ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE (kicks out open transactions). It adds 14 bytes per modified row and version-store load on tempdb.
Check: SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc FROM sys.databases;
PostgreSQL
LevelDirty readNon-repeatable readPhantomSerialization anomalyHow it works
READ UNCOMMITTED✓✗✗✗Accepted but behaves exactly like READ COMMITTED.
READ COMMITTED
default
✓✗✗✗New snapshot for every statement. An UPDATE that waited on a row re-checks its WHERE against the newly committed version, then proceeds.
REPEATABLE READ✓✓✓ (stricter than the standard)✗One snapshot for the whole transaction. Updating a row changed by a concurrent committed transaction → 40001 could not serialize access due to concurrent update. Retry the transaction.
SERIALIZABLE✓✓✓✓Serializable Snapshot Isolation: adds SIReadLock predicate locks (visible in pg_locks, they never block) and aborts one transaction with 40001 when a dangerous pattern appears. Needs a retry loop.
Aurora PostgreSQL: set the default with default_transaction_isolation in the DB/cluster parameter group (dynamic) or per session/transaction. Check: SHOW transaction_isolation;

📈 Lock granularity and escalation

SQL Server

Locks form a hierarchy. Before taking a row lock, SQL Server takes intent locks (IS / IX) on the page and table, so a later table lock request can see the conflict without scanning every row lock.

DATABASE→OBJECT (table)→HoBT / partition→PAGE→KEY / RID (row)

Escalation goes from row or page locks straight to the table (or partition with LOCK_ESCALATION = AUTO). It never escalates row → page.

  • Trigger: one statement holds about 5,000 locks on a single table or index (row + page locks on that object), or lock memory crosses the engine's memory threshold.
  • If escalation can't get the table lock because of another session's conflicting lock, the statement keeps its row/page locks and tries again after every 1,250 additional locks.
  • Fixes: batch DML in chunks under ~4,000 rows, add an index so fewer rows are touched, LOCK_ESCALATION = AUTO on partitioned tables. DISABLE (or trace flags 1211/1224) trades blocking for lock memory; use with care.
  • Optimized locking (Azure SQL Database and SQL Server 2025, needs ADR) holds one TID lock per modified row only until the row is updated, which mostly removes escalation. Not available on SQL Server 2019/2022, including RDS.
RDS SQL Server: there is no startup-parameter access; global trace flags are set through the DB parameter group, and only the flags AWS lists for your engine version are allowed (check whether 1211/1224 are among them). Prefer per-table ALTER TABLE … SET (LOCK_ESCALATION = …) and batching. See escalation counts per index with sys.dm_db_index_operational_stats (query in the Diagnostics section).
PostgreSQL

PostgreSQL never escalates. Row locks are written into the row itself, so locking a million rows costs no lock memory. What uses the shared lock table is one entry per relation (table, index, partition) per transaction, plus advisory and other object locks.

  • Shared lock table size ≈ max_locks_per_transaction × (max_connections + max_prepared_transactions). One transaction may use more than 64 as long as the total fits. When it runs out: ERROR: out of shared memory · HINT: You might need to increase max_locks_per_transaction.
  • Common triggers: pg_dump of thousands of tables, queries over heavily partitioned tables without pruning, a big DROP SCHEMA … CASCADE.
  • Fast-path: each backend keeps its first 16 weak locks (AccessShare / RowShare / RowExclusive) outside the shared table (PG ≤ 17; PG 18 sizes it from max_locks_per_transaction). Beyond that, heavy partition use shows up as LWLock:LockManager waits.
  • Advisory locks are app-defined: pg_advisory_lock(key) (session, must unlock), pg_advisory_xact_lock(key) (released at commit), pg_try_advisory_lock(key) (no wait, returns boolean). They appear in pg_locks with locktype = 'advisory'.
Aurora PostgreSQL: max_locks_per_transaction is static, so changing it in the cluster/DB parameter group needs a reboot. In Performance Insights, lock waits appear as Lock:relation, Lock:tuple, Lock:transactionid, Lock:advisory; lock-manager contention as LWLock:lock_manager / LWLock:LockManager.

🩺 Diagnostic queries

All read-only except the cards marked ⚠ Destructive. Look first; kill only once you know what the head blocker is doing.

SQL Server
PostgreSQL / Aurora PG

Based on the Microsoft "Transaction locking and row versioning guide" and the PostgreSQL "Explicit Locking" / "Transaction Isolation" chapters. Lock IDs, page numbers and timings in the simulator are illustrative. ← Back to the DBA Cheat Sheet