← All topics
sql
4 posts tagged “sql”.
TIL: deadlocks — the fix is lock order, not speed
Two transactions deadlock when their lock order forms a cycle — the fix is ordering locks consistently, not going faster.
til
TIL: MVCC — why reads don't block writes
Postgres lets reads and writes proceed without blocking by keeping multiple row versions, cleaned up later by VACUUM.
til
TIL: pessimistic vs optimistic locking
SELECT ... FOR UPDATE locks a row until commit; optimistic locking guards it with a version column instead.
til
TIL: primary, unique, and foreign keys — the parts I had assumed wrong
Primary keys are unique + NOT NULL; foreign keys can reference any unique column and aren't auto-indexed in Postgres.
til