1. Rollbacks, savepoints, and exceptions
In addition to the recording and logging exceptions, it is also common to want to issue rollbacks and leverage savepoints, which let us to partially rollback a transaction to a known good state, in the exception handler. However, A transaction can not be ended inside a block with exception handlers. Let's dive in to wrap our minds around this.
2. Automatically rolls back
For example, here we are updating the deliciousness of Cats Tongue and Gingerbread cookies and we want both of the statements to succeed or fail together.
However, as we learned in Spinal Tap, the movie, "It goes to 11", which means that the gingerbread update statement is going to violate the constraint on the deliciousness column.
Hopefully, you notice this is the same pattern we've been applying. By default, an exception handler issues a rollback on the code, which means that these cookies will be at the same deliciousness as they were before this function began.
If we can't end a transaction inside a block with an exception handler, what do we do for savepoints?
3. Emulating savepoints
4. Emulating savepoints
We can emulate savepoints by nesting multiple blocks inside a block! For example, if we wanted to update the costs on five of our cookies in two different batches, and we had a constraint of 10 on the cost column. We start out our Do function as normal, and then instead of putting our statements right after the BEGIN keyword, we nest another BEGIN/EXCEPTION/END block with our first three statements in it. Not how Block 1 follows the same formula that we had with our prior exception handers. If this block succeeds, it is like having made a savepoint after these three statements.
5. Emulating savepoint continued
Now we add the second BEGIN/EXCEPTION/END block with our two additional update statements. Notice the first one has a cost higher than our constraint of 10. So this will cause an exception that will rollback the two statements in block 2; however, it will not rollback the statements in block 1, which is just like a rollback to a savepoint after block 1. Now we can END the outer block and specify our language. There is no limit to the number of blocks you can nest inside the outer block; nonetheless, it's wise to limit the number of nest blocks to less than 3 or 4 to ensure your code is still easy to reason about.
6. A quick aside
Most of the errors we've been handling are the direct result of the statements we are typing in. We are doing this intensionally to let us focus on the control of errors and handling of exceptions. These types of issues occur when working with outside datasets that contain dirty data, passed in variables with wrong values, or the incorrect use of a field. So while you can quickly fix a typo in our exercises, that's not the case in production usage.
7. Let's practice!
Now it's time to apply what we just learned.