1. Transaction isolation levels
In this final chapter, we will study what concurrency is and how it can affect transactions.
2. What is concurrency?
Concurrency happens when two or more transactions that read or change shared data are executed at the same time.
Depending on the purpose of the transactions, we may want to isolate our transaction from other transactions.
3. Transaction isolation levels
We can achieve different levels of isolation using the Transaction isolation levels that SQL Server provides. These are: READ COMMITTED, which is the default level, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, and SNAPSHOT.
This is the syntax for setting an isolation level. It starts with SET TRANSACTION ISOLATION LEVEL followed by the specific isolation level.
4. Knowing the current isolation level
If we want to know the current transaction isolation level of our session, we can select the transaction_isolation_level column from sys.dm_exec_sessions. This column returns a different value depending on the transaction isolation level.
In this example, the current isolation level is READ COMMITTED.
5. READ UNCOMMITTED
In this lesson, we will explain READ UNCOMMITTED, and the rest of the isolation levels will be explained in the next lessons.
READ UNCOMMITTED is the least restrictive isolation level. It allows you to read rows modified by another transaction which hasn't been committed or rolled back yet.
6. READ UNCOMMITTED
Under READ UNCOMMITTED, it is possible to encounter dirty, non-repeatable, or phantom reads. These are called concurrency phenomena because they occur when two or more transactions are running concurrently, so data that might have been changed by one transaction is read by another.
7. Dirty reads
To understand what dirty reads are, let's suppose that account 5 has $35,000. One person starts a transaction, Transaction1, and updates the current_balance of account 5 to $30,000, but Transaction1 doesn't commit or rollback yet. After this update, another transaction, Transaction2, selects the current_balance of account 5, under READ UNCOMMITTED. As this isolation level allows to read uncommitted changes, Transaction2 sees that the current_balance of account 5 is $30,000, that is, a dirty read. After that, if Transaction1 rollbacks, the current_balance of account 5 doesn't change. However, Transaction2 still thinks that the current_balance is $30,000.
8. Non-repeatable reads
READ UNCOMMITTED also allows non-repeatable reads.
Let's suppose that Transaction1 selects the current_balance of account 5, which is $35,000. After that, Transaction2 updates the current_balance of account 5 to $30,000 and commits.
9. Non-repeatable reads
If Transaction1 wants to select the current_balance of account 5 again, it will output $30,000, resulting in a different value from the first time it selected the current_balance of account 5.
This difference can cause problems if we make a business decision based on the first result.
10. Phantom reads
Finally, let's explain phantom reads. Let's imagine one person starts Transaction1 under READ UNCOMMITTED and selects accounts with a balance between $45,000 and $50,000. There is only one account that matches that criterion.
After some seconds, another person opens Transaction2 and inserts one account which fulfills the selection criteria of Transaction1, adding one account with a balance of $45,000 and commits.
11. Phantom reads
After that, if Transaction1 wants to select accounts with a balance between $45,000 and $50,000 again, it will get one additional account, the phantom read. This result is different from the first one.
This difference can also cause problems if we make a business decision based on the first result.
12. READ UNCOMMITTED - summary
Let's finish with the pros, cons, and when to use READ UNCOMMITTED.
READ UNCOMMITTED can return the results of a query faster than using other isolation levels. As we will see, other isolation levels can block other transactions, so the information may not be returned immediately.
However, it allows dirty, non-repeatable, and phantom reads.
You can use READ UNCOMMITTED when you don't want to be blocked by other transactions, but don't mind concurrency phenomena. You can also use it when you explicitly want to watch uncommitted data.
13. Let's practice!
Let's practice!