Introduction

Isolation levels are one of those topics that are often poorly understood or even more commonly just neglected. On the other hand, a clear understanding of trade-offs is crucial to creating a correct application.

But let's start from the beginning. Typically we think of databases like SQL Server or MySQL as safe, full ACID and transactional. Of four letters I (Isolation) seems to be most tricky to get right. True isolation limits concurrency severely, for some applications to an unacceptable level.

That is why by default databases choose to enforce only basic isolation and let more elaborate, if you will, scenarios, to be at risk of some data corruption. I will show you one of the common ways that let some inconsistencies to show up in production databases.

Example

The scenario is abstract but typical to many business processes. First, we will verify if some conditions are met, and then based on those conditions we will insert a new row to the database.

Let's say that we have a gas tank, but it is limited to 10000 m3. We will register injections and withdrawals in GasOrders table. Injections will be represented as positive orders, withdrawals as negative ones.

CREATE TABLE GasOrders(Volume_m3 INT NOT NULL);

Before any injection order is accepted we check if it safe, that is whether the limit of 10000 m3 would not be exceeded. This script executes the process, part A is checking the safety of injection, part B saves the order.

---------------
-- PART A - checks whether injection is accepted
--------------
BEGIN TRANSACTION

DECLARE @injection_m3 INT = 9999; 
DECLARE @currentSum INT = ISNULL((SELECT SUM(Volume_m3) FROM GasOrders), 0);
-- temp table used to keep data between batches
CREATE TABLE #acceptedOrder(Volume_m3 INT NOT NULL);
IF(@currentSum + @injection_m3 < 10000) 
BEGIN
    INSERT INTO #acceptedOrder(Volume_m3) VALUES(@injection_m3);
END
---------------
-- END OF PART A
--------------

---------------
-- PART B - saves order and commits transaction
--------------
INSERT INTO GasOrders(Volume_m3) 
SELECT Volume_m3
FROM #acceptedOrder;

DROP TABLE #acceptedOrder;

COMMIT TRANSACTION
---------------
-- END OF PART B
--------------

SELECT * FROM GasOrders

Of course running this script serially, without any concurrency works absolutely fine. If the order is about to exceed the limit it is rejected. However, when we introduce the second client, which tries to inject in parallel, things can break down pretty fast.

One of the scenarios that reveal faulty behaviour is as follows:
1. For simplicity assume we start with no gas in task (GasOrders is empty)
2. Client 1 executes part A of the script, injection is accepted. No lock is held on GasOrders after that.
3. Client 2 executes part A and B of the script, injection is accepted and saved. Still, no lock is held on GasOrders after transaction is completed.
4. Client 1 executes part B of the script, the second injection is saved, exceeding the global limit.

As you can see default isolation level READ COMMITTED does not prevent unrepeatable-read.

Mitigation

In given example, raising the isolation level to REPEATABLE READ solves the problem completely. It does, however, limit concurrency, as a shared lock is kept on GasOrders after the first SELECT till the end of the transaction.

Summary

Isolation levels lower than SERIALIZABLE do trade security for performance. What is crucially important is that problems related to them tend to reveal themselves only in production environments with lots of parallel operations. That is why they are hard to find during regular tests. I would even say, that those bugs are just another kind of Heisenbug

The only way to avoid them is to understand isolation levels in detail and mindfully review all the code that queries the database(does not matter if it is SQL or application language).