⚖️ SQL Server vs PostgreSQL, side by side
The same questions, answered for each engine. Use the engine switch above to drill into one.
| Question | SQL 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 store | tempdb (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 lock | Sch-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 default | SET LOCK_TIMEOUT -1 (wait forever). Error 1222 when it fires. | lock_timeout = 0 (wait forever). SQLSTATE 55P03 when it fires. |
| Deadlock detection | Lock 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 locks | sp_getapplock / sp_releaseapplock | pg_advisory_lock, pg_advisory_xact_lock, pg_try_advisory_lock |
| "Who blocks whom" view | sys.dm_exec_requests.blocking_session_id, sys.dm_tran_locks | pg_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 blocker | KILL <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.
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.
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…
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).
▶️ 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.
| Level | Dirty read | Non-repeatable read | Phantom | How 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. |
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;| Level | Dirty read | Non-repeatable read | Phantom | Serialization anomaly | How 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. |
default_transaction_isolation in the DB/cluster parameter group (dynamic) or per session/transaction. Check: SHOW transaction_isolation;📈 Lock granularity and escalation
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.
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 = AUTOon 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.
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 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_dumpof thousands of tables, queries over heavily partitioned tables without pruning, a bigDROP 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 asLWLock:LockManagerwaits. - 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 inpg_lockswithlocktype = 'advisory'.
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.
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