Part 159: Latches vs Locks: Buffer Latches, Spinlocks & Page Splits
Understanding the micro-concurrency primitives running inside database memory engines.
1. Locks vs Latches: What is the Difference?
| Feature | Locks (Logical) | Latches (Physical) |
|---|---|---|
| Managed By | Lock Manager component | Storage Engine memory manager |
| Protects | Logical database objects (Rows, Tables, Key Ranges) | Physical in-memory data structures (8KB/16KB memory pages, buffer pool hash tables) |
| Duration | Transaction lifetime (until COMMIT or ROLLBACK) |
Microseconds (released immediately after memory pointer is updated) |
| Deadlock Detection | Yes (Engine runs deadlock detection background thread) | No deadlocks expected; code ordering guarantees strict hierarchy |
| Visibility | Exposed via SQL (pg_locks, sys.dm_tran_locks) |
Exposed via engine wait statistics (PAGEIOLATCH, LATCH_EX) |
2. Page Splits and B-Tree Fragmentation
When you insert a row into an already full 8KB or 16KB leaf page in a B-Tree index: 1. The engine acquires an exclusive page latch. 2. A new page is allocated on disk. 3. Approximately 50% of the rows from the full page are copied over to the new page (Page Split). 4. Pointers in the parent node are updated. 5. Latches are released.
-- Random UUID insertions cause frequent page splits across random leaves!
-- Sequential monotonically increasing IDs (BIGSERIAL, IDENTITY, ULID)
-- only split at the rightmost edge, maximizing page fill factor and write speed.
Key Takeaways
- High
PAGEIOLATCHwaits indicate disk I/O bottlenecks bringing pages from disk into the buffer pool. - High
PAGELATCHwaits indicate in-memory hot-page contention (e.g. multiple threads fighting for the exact same page in RAM).
In Part 160, we explore Deadlock Graph Analysis, Lock Escalation & Zero-Deadlock Patterns!