Around 2 months ago before, a coworker approached me: “Hullo, I have an application (with Postgres database) that seems to experience race conditions on one user’s data when the user creates parallel sessions to handle their data at once, how should I prevent that? 😶”
Intuitively, I replied: “Transactions!”
But on deeper thought and discussion, I realized that while the right answer is indeed along the lines of database transactions, implementing it wasn’t what I thought it was!
Join me in this TIL, where I record my learning here (and hopefully it helps you out too!
Let’s start with transactions and “Write locks”
When learning about database transactions in ACID-compliant databases like PostgreSQL, we learn about how these transactions are supposed to be atomic. For example, the following code block shows a transaction of an update and delete operation:
BEGIN;
UPDATE sometable SET someproperty = somevalue WHERE anotherproperty = anothervalue;
DELETE anothertable WHERE someproperty = anothervalue;
COMMIT;There are two things that we gain from running these commands in a single transaction:
- All commands in a single transaction are committed together…
