Foreign keys, isolation levels, and Van Halen
summary
Claim: foreign keys are a practical litmus test—like the brown M&M rule—for whether a database actually enforces transactional invariants. Demonstrates using manual existence checks, application-level status checks, and explicit FOR SHARE lock semantics versus relying on database-enforced foreign key constraint checks. Points out SERIALIZABLE guarantees the manual pattern works without racing, while weaker isolation requires locks. Notes many sharded systems forbid foreign keys, which is a concrete red flag about correctness and operational tradeoffs.
AI-written summary · about
chapters (10)
- 0:00Welcome/Introduction
- 0:15What was Van Halen brown M&M's policy in their rider?
- 2:02Why foreign key constraints are similar to Van Halen's brown M&M rule
- 2:20What are foreign key constraints?
- 4:49INSERT with foreign key constraints
- 5:21Manual foreign key constraints
- 6:55Application level constraints
- 7:57Concurrency (Safe in SERIALIZABLE isolation)
- 9:23Explicit locking
- 11:26What do foreign key constraints have to do with Van Halen?
related talks
58mConcurrency in Postgres
14mChecking Observational Correctness of Database Systems
45mTransactions and Concurrency Control Patterns
59mSQL primary key, surrogate key, composite keys, foreign keys... and JPA
29mPutting Rails in a corner: Understanding database isolation
14mMonkeyDB: Effectively Testing Correctness under Weak Isolation Levels
41mTransactions: myths, surprises and opportunities
35mDon't Give Up on Serializability Just Yet