Two customers buy the last item in stock at the same second. Both orders go through. Every line of your code ran inside a transaction, and SQL Server did exactly what its default setting promises.
That setting is the isolation level . There are four of them. This post walks through each one, the anomaly it stops, and the .NET class that quietly picks the strictest one for you.
A transaction isn't a freeze
A transaction promises all or nothing : your changes land together, or not at all. What it doesn't promise is that the world stops while you work. Other transactions keep reading and writing while yours runs.
How much of their work you can see is a dial, and that dial is the isolation level. It has four steps:
Read uncommitted
Read committed
Repeatable read
Serializable
Each step up protects you from one more anomaly.β¦