1. READ COMMITTED & REPEATABLE READ
In this lesson, you will be introduced to the READ COMMITTED and the REPEATABLE READ isolation levels.
2. READ COMMITTED
READ COMMITTED is the default isolation level in SQL Server.
Under this isolation level, you can't read data modified by other transaction that hasn't committed or rolled back yet.
This is the syntax for setting the READ COMMITTED isolation level.
3. READ COMMITTED - isolation level comparison
If you remember from the previous lesson, we learned that under the READ UNCOMMITTED isolation level, we could find dirty reads, non-repeatable reads, and phantom reads. The READ COMMITTED isolation level is more restrictive than READ UNCOMMITTED in the sense that READ COMMITTED prevents dirty reads. Let's demonstrate this behavior.
4. READ COMMITTED - preventing dirty reads
Let's suppose that account 5 has $35,000. One person starts 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 the READ COMMITTED isolation level. As this isolation level doesn't allow us to read uncommitted changes, Transaction2 is blocked and has to wait until Transaction1 commits or rollbacks, preventing the dirty read.
5. READ COMMITTED - preventing dirty reads
After that, when Transaction1 commits or rollbacks, Transaction2 immediately gets the output of the select query.
6. READ COMMITTED - selecting without waiting
Let's see how READ COMMITTED behaves when two transactions select data but don't change that data.
In this example, Transaction1 starts and selects the current_balance of account 5, but doesn't commit or rollback yet. After that, Transaction2 selects the current_balance of account 5, under the READ COMMITTED isolation level. Since Transaction1 just made a selection without updating or deleting data, it didn't block Transaction2. Transaction2 can immediately get the result without waiting.
7. READ COMMITTED - summary
As we have seen, READ COMMITTED prevents dirty reads. However, it allows non-repeatable and phantom reads. You can also be blocked by another transaction. You can use READ COMMITTED if you want to ensure that you only read committed data, not non-repeatable and phantom reads.
8. REPEATABLE READ
Let's study the REPEATABLE READ isolation level.
As with READ COMMITTED, you can't read uncommitted data from other transactions. Besides, if a transaction reads some data under this isolation level, other transactions cannot modify that data until the REPEATABLE READ transaction finishes.
9. REPEATABLE READ - isolation level comparison
Let's review the isolation levels we learned previously. Under the READ UNCOMMITTED level, dirty reads, non-repeatable reads, and phantom reads can happen. The READ COMMITTED isolation level prevents dirty reads but still can have non-repeatable and phantom reads. With the REPEATABLE READ isolation level, we can prevent dirty reads as READ COMMITTED does, but we can also prevent non-repeatable reads. Let's demonstrate how this isolation level can prevent non-repeatable reads.
10. REPEATABLE READ - preventing non-repeatable reads
In this example, Transaction1, under the REPEATABLE READ isolation level, selects the current_balance of account 5, getting the result. After that, Transaction2 starts and tries to update the current_balance of account 5 to $30,000. However, Transaction2 is blocked and has to wait to perform the update until Transaction1 finishes.
11. REPEATABLE READ - preventing non-repeatable reads
Now, if Transaction1 fires the same select statement again, it gets the same result as the first select statement, without getting a non-repeatable read.
12. REPEATABLE READ - preventing non-repeatable reads
When Transaction1 finishes, Transaction2 can finally perform the update.
13. REPEATABLE READ - summary
As we have seen, REPEATABLE READ prevents other transactions from modifying the data you are reading. This prevention results in not having non-repeatable reads. It also prevents dirty reads.
However, it allows phantom reads. You can be blocked if you are trying to modify the data that a REPEATABLE READ transaction is reading.
You can use REPEATABLE READ if you want to ensure you only read committed data and don't want other transactions to modify what you are reading. You don't care if phantom reads occur.
14. Let's practice!
Let's put READ COMMITTED and REPEATABLE READ into practice!