T-SQL: SQL Server Transaction Isolation Levels

There are 4 attributes ensured to a transaction (ACID):

A: Atomicity - In a transaction either all included elements will get committed or none of them
C: Consistency - Data integrity is ensured, meaning if any failure occurs during the transaction, the previous state at begin of the transaction will be kept.
I: Isolation - A transaction in process may not be affected by other transactions.
D: Durability - Committed data is saved by the system.

There are 4 different isolation levels. A sample with SQL code for each type will be provided.

I will use the following Artist table as a basis for my samples:

SELECT [ArtistID]
               ,[Artist]
FROM [Tutorials].[stg].[Artist]SELECT [ArtistID]

ArtistID    Artist
----------- ------------
1           00Agents
2           06 Style
3           10°
4           1000 Ohm
.......

1) READ UNCOMMITTED
This isolation level allows to read changed data from other not closed transactions, which may later be rolled back.

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

The query delivers the following result:
ArtistID    Artist
----------- -----------
1           00Agents

(1 row(s) affected)

Now I will run the following transaction, but won't commit the transaction:

Transaction 2:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')

If I run the first query again, I will now also see the inserted value from above (not committed transaction)

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

ArtistID    Artist
----------- -----------
1           00Agents
38         00Agents

(2 row(s) affected)

Instead of committing I will now rollback the transaction. Now we see the original result again.

----------- -----------
1           00Agents

(1 row(s) affected)

This scenario where uncomitted transactions can be captured, is often referred to as a "Dirty Read".


2) READ COMMITTED
This is the standard default value in SQL Server.
Here we are able to only see committed transactions:

Assuming I'm inserting values in a table, and I open a second transaction where I want to read the table, the following will happen.

Here we insert the values (but do not commit right away for demonstration reasons):

Transaction 1:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')


Now we want to select values from that same table:


Transaction 2:
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

Transaction 2 will deliver no result until Transaction 1 is finished.

Once I commit the transaction with the insert values, the select query (Transaction 2) shows results:

Transaction 2:
--BEGIN TRANSACTION

--INSERT INTO [Tutorials].[stg].[Artist]
--VALUES
--('00Agents')

COMMIT TRANSACTION

ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)


3) REPEATABLE READ
This isolation level gurantees that data read in a transaction will deliver the same result set later in that transaction. In the sample below I will have a SELECT query, add a waitfor delay and will start a second session in that break that will update values in the underlying table and then after the break run the same SELECT statement in Session 1 to see if the changes happend.

Transaction 1
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ

BEGIN TRANSACTION

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

WAITFOR DELAY '00:00:05'

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

COMMIT TRANSACTION

 Transaction 2:

BEGIN TRANSACTION

UPDATE [Tutorials].[stg].[Artist]
SET Artist = '01Agents'
WHERE ArtistID = 1

COMMIT TRANSACTION


Although we have an update between the 2 queries, the result is the same.


ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)

ArtistID    Artist
----------- ----------

1            00Agents
39          00Agents

(2 row(s) affected)The reason why the change happens after Transaction 1 is finished, is due to a share lock that is kept until the transaction with Isolation Level REPEATABLE READ is finished.

4) SERIALIZABLE
This  Isolation Level is quite comparable with REPEATABLE READ. The main difference is that Isolation Level SERIALIZABLE wouldn't allow inserted rows. Updates and Deletes are locked with REPEATABLE READS, but inserts are technically possible - but not with Isolation Level SERIALIZABLE. This scenario is also known as a "Phantom Read".