Understanding SQL Database Isolation Levels
Database isolation is a property that defines how and when the changes made by one operation become visible to other concurrent operations. Isolation is one of the ACID (Atomicity, Consistency, Isolation, Durability) properties.
There are four isolation levels in SQL databases, as defined by the SQL standard:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
Let’s explore each of these levels in detail, including their pros and cons and the circumstances in which they might be used.
In order to demonstrate isolation levels, let’s consider a scenario where two transactions are operating on the same row in a table. We’ll use a simple table called Accounts with columns AccountID, Name and Balance. Let’s assume there’s an account with AccountID 1, having the name Alice and Balance 100.
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance – 10 WHERE name = ‘Alice’;
— Balance is now 90
— This transaction can read the uncommitted data from Transaction 1
SELECT Balance FROM Accounts WHERE name = ‘Alice’;
— Returns 90