Get startedGet started for free

READ COMMITTED & REPEATABLE READ

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!