1. Isolation levels, savepoints, and rollbacks
Great work on that last exercise! Now let's look learn about how Isolation Levels can affect savepoints and rollbacks.
2. Isolation Levels
At the end of chapter 1, you learned about different isolation levels and practiced using REPEATABLE READS and SERIALIZABLE isolation level.
3. Repeatable Read
You might recall that you learned about the SERIALIZABLE isolation level at the end of Chapter 1.
Serializable treats the transactions as if transactions had been executed one after another, serially, rather than concurrently. This time, you are going to learn about the REPEATABLE READ isolation level. The Repeatable Read isolation level only sees data committed before the transaction began and the effects of previous updates executed within its transaction. To make that a bit clearer, if you have multiple statements in the same transaction, a statement lower in the transaction can see the results of the prior statements in that same transaction. It never sees any data committed or not from external transactions.
4. Visualizing REPEATABLE READ
To see the effect of using REPEATABLE READ more clearly, let's look at an example where you have two different transactions altering the same records. When transaction one on the left begins, we declare it as a repeatable read transaction. It makes a snapshot of the records when it starts. Simultaneously Transaction 2 on the right moves into its initial statement, which reduces the RCON2237 field by 10%. Now Transaction 1 performs the first statement starting from its snapshot and reduces by the snapshot by 35%.
5. Visualizing REPEATABLE READ
Transaction 2 commits it's updated record to the table. The second statement in the first transaction operates on its snapshot and reduces the record by an additional 5%. Now is where the isolation level becomes critical! Transaction 1 will not know about the change that transaction 2 has made because it's set to repeatable read and will only operate on the result of the first statement applied to the snapshot of the data taken at the beginning of the transaction. As an aside, if transaction 1 had reread the committed data from Transaction 2, that is a Nonrepeatable Read issue, which is where this isolation level gets its name.
6. Visualizing REPEATABLE READ
When transaction 1 attempts to commit its results, it failed because the data in the table changed while transaction 1 was running. If this had an isolation level that didn't protect from non repeatable reads, such as READ COMMITTED and UNCOMMITTED the second statement in transaction 1 would have seen the data updated by transaction 2 and completed a further reduction by 5%, which would have reduced the data more than you desired. Using REPEATABLE READ ensured that you would work from the same starting data for both statements and would succeed or fail as a group.
7. REPEATABLE READ
In this example, you are starting a transaction with a REPEATABLE READ isolation level. So, the first statement in the transaction reduces Macaron cookies by 4 for an order. Then second statement reduces all cookies by a dozen for a second order. Because this was done in a REPEATABLE READ isolation level when the second statement runs, it sees the results first statement only. No other alterations to the data are visible or affect this reduction from outside of this transaction. If an outside modification by another transaction happens between the two statements, the second statement will error. This prevents the cookie quantity from changing between the two statments.
8. Let's practice!
Now that you've seen repeatable read in action, let's go use it!