March 8, 2026 1 min read
PostgreSQL Advisory Locks: Preventing Double-Bookings
Deep Dive
Featured
Depth: ●●●○○
Three race conditions, three PostgreSQL mechanisms: advisory locks for bookings, SELECT FOR UPDATE for gift cards, CHECK constraints as last defense.
1. Advisory Locks for Bookings
pg_advisory_xact_lock(hashtext(staffId + ":" + locationId)) serializes booking attempts. Lock key includes locationId so different locations don't block. Why not unique constraints: overlapping bookings don't have the same starts_at — overlap is a range query requiring serialization.
2. SELECT FOR UPDATE for Gift Cards
Exclusive row lock → check balance → update → audit trail. CHECK constraint (balance_in_cents >= 0) as database-level safety net even if application logic has bugs.
3. Advisory Locks for Loyalty Points
pg_advisory_xact_lock(hashtext(userId)) serializes all point mutations per user. Edge case tests verify all guarantees.