Design for implicit and explicit transactions.
This objective may include but is not limited to: nested transactions, savepoints, TRY/CATCH
I think that the main point here is more on what implicit and explicit transactions are than the bullet points beneath. I will still go over each of them, but it is EXTREMELY important to know about transactions. Knowing about transactions includes knowledge of what happens during a nested transaction and with save points. I’m not exactly sure why the TRY/CATCH blocks were included with this section, but I use them with most data modification scripts and they are helpful with transactions.
Transactions represent the single unit of work, comprised of one or more statements, which will abide by the ACID (Atomicity, Consistency, Isolation, Durability) principle. Transactions should be used to keep the database in a consistent state. For example, if you were to attempt to update an order table to show that we have ordered a t-shirt, followed by decreasing the remaining balance on an account table, you would want to ensure that this action occurred as a single unit of work so that the system would not mark the order without removing the money (or vice versa).
When running normal T-SQL commands you would generally use the default mode, Autocommit Transactions. In this mode, each statement which completes successfully is committed. Each statement which encounters an error is automatically rolled back.
Implicit transactions are used when the SET IMPLICIT_TRANSACTIONS command is run with a parameter of ON. With implicit transactions, a new transaction is automatically started when the current one is committed or rolled back. If there is no open transaction one will automatically be created when the code encounters one of the following statements: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE or UPDATE. Implicit transactions still require the user to commit or roll-back the transaction.
With Explicit transactions, the user is expected to define both the start and end of the transaction through the BEGIN TRANSACTION, COMMIT TRANSACTION (or COMMIT WORK), and ROLLBACK TRANSACTION (ROLLBACK WORK) statements.
Nested Transactions are a bit of a fallacy in SQL Server. It is true that transactions can be created that are nested, and that these transactions can be committed, but it isn’t REALLY committed until the outermost transaction is committed. If there is an error after the procedure which ran the inner transaction completed it will still roll back the inner transaction as well. Furthermore, you cannot roll back named transactions other than the outermost applicable transaction.
Transaction Savepoints allow for a portion of the transaction to be rolled back. According to books online, savepoints are useful when the likelihood of encountering an error is small but the cost of checking to see if there would be an error is large. However, from reading Itzik Ben-Gan’s books I have found the most useful aspect of this to be that it is a construct which could be used in lieu of the nested transaction mentioned above such that only the work to be done in the inner portion of the transaction will be rolled back, if necessary, rather than all of the rest of the work which the transaction has accomplished as well.
TRY CATCH BLOCK
We discussed many of the aspects of the TRY … CATCH block in the 70-451 – MCITP – Designing Programming Objects – Stored Procedures post. However, it should be apparent that this construct will allow us to more properly handle transactions with the appropriate begin, commit and rollback operations without having to check for errors after every statement.
Here is a simple example (this is not the full template I usually use):
BEGIN TRANSACTION Simple;
Update dbo.foo Set foo.bar = ‘Yes’;
COMMIT TRANSACTION Simple;
ROLLBACK TRANSACTION Simple;