1. SNAPSHOT
In this final lesson, you will study the SNAPSHOT isolation level, the READ COMMITTED SNAPSHOT option, and the WITH (NOLOCK) option.
2. SNAPSHOT
Under SNAPSHOT, every time we modify a row, SQL Server stores the previous version of the committed row in tempDB.
You can only see the committed changes that occurred before the start of the SNAPSHOT transaction and the changes made by that transaction. Therefore, you can't see any changes made by other transactions after the start of the SNAPSHOT transaction. SNAPSHOT transactions that read data don't block other transactions that write data, and transactions that write data don't block SNAPSHOT transactions that read data. Using SNAPSHOT, we can have update conflicts if two transactions try to update the same data at the same time.
To use it, you must activate the ALLOW_SNAPSHOT_ISOLATION option.
Here you can see the syntax for SNAPSHOT.
3. SNAPSHOT - isolation level comparison
Similar to SERIALIZABLE, SNAPSHOT prevents dirty, non-repeatable, and phantom reads, with the difference that SNAPSHOT doesn't block transactions as SERIALIZABLE does.
4. SNAPSHOT - example
Let's see how SNAPSHOT behaves. In this example, Transaction1 under the SNAPSHOT isolation level, starts selecting every account without committing yet. After that, Transaction2 inserts a new account, updates the current_balance of another account, and selects every account. Transaction2 isn't blocked and can perform every operation.
5. SNAPSHOT - example
If Transaction1 selects every account again, it gets the same output as in the previous select statement. We don't see the data changed by Transaction2 because these changes occurred after the start of Transaction1, and with SNAPSHOT we can only see the committed changes that occurred before the start of the SNAPSHOT transaction and the changes made by that transaction. Neither Transaction1 nor Transaction2 was blocked.
6. SNAPSHOT - summary
Let's summarize SNAPSHOT. It prevents dirty, non-repeatable, and phantom reads, but doesn't block transactions.
By contrast, tempDB increases because every modification is stored in it.
You can use SNAPSHOT when data consistency is a must, and you don't want blocks.
7. READ COMMITTED SNAPSHOT
Let's see the READ COMMITTED SNAPSHOT option. It changes the behavior of the READ COMMITTED isolation level.
This is the syntax to configure it.
The default option is OFF.
To use the ON option, you also need to activate ALLOW_SNAPSHOT_ISOLATION.
If we set READ COMMITTED SNAPSHOT to ON, each statement under the READ COMMITTED isolation level, will see the committed changes that occurred before the start of each statement. Remember that the SNAPSHOT isolation level did it for the start of the transaction, not the statement.
If we use READ COMMITTED SNAPSHOT, we can't have update conflicts if two transactions try to update the same data at the same time. Remember, these conflicts can occur using the SNAPSHOT isolation level.
8. READ COMMITTED SNAPSHOT - example
Let's see an example. Let's suppose we set READ_COMMITTED_SNAPSHOT to ON, and account 1 has $35,000.
Transaction1 and Transaction2 start at the same time under READ COMMITTED. Transaction1 updates the current_balance of account 1 to $30,000 without committing yet. After that, Transaction2 selects the current_balance of account 1, getting $35,000 as the output. After that, Transaction1 commits.
9. READ COMMITTED SNAPSHOT - example
When Transaction2 selects the current_balance of account 1 again, the committed value, $30,000, is returned because this committed change occurred before the start of that select statement.
10. WITH (NOLOCK)
Let's end explaining the WITH (NOLOCK) option. Similar to READ UNCOMMITTED, WITH (NOLOCK) is used to read uncommitted data. READ UNCOMMITTED applies to the entire connection, but WITH (NOLOCK) applies to a specific table. You can use it under any isolation level when you just want to read uncommitted data from specific tables.
Let's see an example.
11. WITH (NOLOCK) - example
Let's suppose account 5 has $35,000. Transaction1 starts and updates the current_balance of account 5 to $30,000, without committing or rolling back yet. After this update, Transaction2 selects the current_balance of account 5 using WITH (NOLOCK). Transaction2 sees that the current_balance is $30,000, although Transaction1 didn't commit yet. If we didn't use WITH (NOLOCK) in Transaction2, the select statement would behave under the set isolation level.
12. Let's practice!
Let's practice!