Row-level locking with SELECT ... FOR UPDATE in a transaction

Optimistic locking is great for most user edits, but sometimes you need strict serialization—like decrementing inventory or consuming a one-time token. In those cases I use SELECT ... FOR UPDATE inside a transaction. The lock is scoped to the transaction, which means your code must be disciplined about ctx deadlines so locks don’t hang forever. The other key is ordering: if you lock multiple rows, lock them in a consistent order to avoid deadlocks. This pattern also pairs well with idempotency keys: acquire the row lock, check whether the work was already done, then perform the write. When used carefully, it’s a reliable way to enforce invariants without building distributed locks. The database is very good at this; let it do the hard work.