Imagine a bank account with $100. Let’s assume that two separate withdrawal requests of $10 each arrive at the same moment. However, after the two withdrawals are made, the account still ends up $90.
On a purely technical level, both withdrawal requests completed successfully. There was no error. But the account balance is wrong. The remaining balance should have been $80.
On its own, both transactions were correct. Each read the correct balance and performed the correct calculations. And yet, there is a bug. The reason for this bug was the overlap between the two transactions.
You might think this type of overlap is a rare condition. However, this is not true. Overlapping transactions are more or less the normal condition in which databases operate. At any given point in time, multiple processes are trying to write something to a database, often on the same set of records. It just takes two of them to collide with each other inside a window of a few milliseconds for these types of bugs to show up.
So how do we handle such bugs?
This is what we are going to try to answer in this article. Here’s what we will cover:
How does data get corrupted due to multiple transactions?
4 ways the data gets corrupted
Different ways to handle data conflicts
Block everyone up front (Pessimistic Locking)
Gamble and check afterwards (Optimistic Locking)
How databases stopped making readers and writers wait for each other?
Picking the right level of data protection with isolation levels
Strict without slow: the idea that made the safest setting usable
Summary
