Transactions (Part 3)

Good afternoon everyone! This blog post will be the conclusion of my 3-parter series on Transactions, and will concentrate on Transaction Isolation Levels; what they are and how they can be set to improve your transactions.

What are Transaction Isolation Levels? 

Isolation levels are constraints that are applied to a transaction to determine how much access is allowed to resources for data modifications made by other transactions. Usually, transactions will be nested within one another and control will need to be passed between the 2 in order to commit the final data to the database. Isolation levels control: 

• The type of lock required on the transaction. 
• How long the locks are in use for
• When the locks will be freed to allow access from another transaction. 
• Whether other transactions are attempting to access the same data and applies the correct constraints accordingly. 

It's incredibly important to decide which isolation level to use as if the level is too high, blocking or locking can occur (and can be incredibly frustrating when you have multiple users accessing the same information at the same time!). However if the level is too low, potentially many of the users will notice missing updates, deadlock scenarios and what is referred to as "phantom" reads (see section below on Read Phenomena).

Read Phenomena

When a transaction reads data from another transaction that might have been changed, three different scenarios could potentially take place, which are often referred to as the Read Phenomena:

Dirty Reads

A Dirty Read allows a transaction to read data that hasn't been committed to the database yet. This means that the data may be different if the other transaction is rolled back and therefore causing an anomaly. 

Non-Repeatable Reads

A Non-Repeatable Read involves a row being retrieved from the database twice but it may contain different data on each occasion, even though it is retrieving from the same rows within the database. 

Phantom Reads

A Phantom Read occurs when 2 identical queries are run together and the rows returned are different in both result sets, even though the data may be the same. This occurs when the underlying data has been modified at some point in-between the 2 executes (and no read lock has been placed on the data).

Types of Isolation Level

There are various types of isolation level that can be put in place in order to control the amount of Read Phenomena that could occur within the database, which will be discussed in this section: 

Read Uncommitted

Reads data that has been updated by a transaction before it has been committed to the database.

Read Committed

This is the default setting in SQL Server, which allows statements to possibly experience Phantom Reads but not Dirty Reads. 

Repeatable Read

Transactions are only allowed to read data that has already been committed to the database. 

Snapshot

The data is read at the time it has been entered into a transaction (so before any action is performed on it) but no locks are placed on the data. This means that if updates occur, they will not be visible to the transaction that just took the snapshot. 

Serializable

Doesn't allow data to be read before it has been committed to the database and prevents other transactions from accessing data that is being read by the current transaction (until the current transaction has been completed). 

The table below shows the allowances of Read Phenomena depending on the Isolation Level selected: 

How To Declare An Isolation Level? 

Declaring an isolation level is pretty straight forward, and should be done before the transaction is run in order for it to take effect:  

Final Word...

A massive thank you to everyone who has read and continues to follow my blog and my progress as a Junior Developer out in the big wide world! I really appreciate all of your support. If anyone has any questions please don't hesitate to ask me in the comment section below. Plus I like to hear feedback on what I'm doing! Have a good weekend everyone!