After observing a real, live production transaction anomaly a few weeks ago (more on this soon!), I spent yesterday writing an augmentation for our homegrown Go data loading framework that loads records with SELECT ... FOR UPDATE on endpoints that expect to modify or delete them.

While looking for prior art, I came across ActiveRecord::Locking::Pessimistic module, the Rails equivalent of what I was building:

# SELECT * FROM account WHERE id = 1 FOR UPDATE
Account.lock.find(1)

Use of .lock on update/delete actions will prevent a non-repeatable read, the most common transaction anomaly on Postgres’ default read committed isolation level.

But a lot of people probably don’t know that. Transaction anomalies are rare on the whole, and even when they happen probably aren’t diagnosed and/or fixed. It may me wonder how many shops are consciously practicing good locking hygiene versus how many are YOLOing assuming the side effects are rare and fairly benign. We were certainly the latter.