Explicit Transactions - T-SQL

You saw how all insert, update, and delete statements are automatically wrapped into an auto-commit transaction. This means that each of these operations creates a unique entry in the transaction log.

With the exception of the TRUNCATE command, all data modifications are performed in two steps. The two steps are not necessarily sequential and in fact they occur independently of one another. One step is to write all transactions sequentially to the transaction log. This happens quickly because the disk heads don't have to be repositioned and it's not necessary to find free data pages as if writing to a table. The database engine then considers all of the constraints defined for the target table and simply checks to see if the operation would violate those constraints. If not, the transaction succeeds even though the data hasn't been physically written to the table. Any operations that are waiting behind this one are allowed to proceed and users perceive that the database has completed their request.

The second step that occurs is completed by a background process called the checkpoint. The checkpoint occurs at dynamic intervals depending on the amount of data modifications occurring in the database. The more modifications and thus transactions that occur, the more checkpoints are issued by the database engine. A checkpoint can occur anywhere in a transaction or at the end of a transaction. Whenever SQL Server detects that a predetermined number of data pages have been modified, it executes a checkpoint. This setting is adjustable but is beyond the scope of this book. It is the database server's job to balance new transaction requests with pending transactions that have been committed but not yet written to disk by the checkpoint process. When the checkpoint runs, it writes all dirty pages (pages modified by transactions) to disk, but does not release them. The pages are freed when released by a completed transaction. Checkpoints are recorded in the transaction log so SQL Server knows where it left off. When SQL Server is restarted, such as in the case of a power failure or during a database restore, SQL Server finds the last checkpoint in the transaction log and rolls all transactions that committed after the checkpoint forward, writing them to disk. All incomplete transactions that were written to disk during the checkpoint are rolled back, or "undone” so that the database is in a consistent state.

Different operations that need to be processed as a unit should be executed within a stated transaction. For example, if you plan to move a group of records from one table to another, you don't want to insert rows into one table if the corresponding delete doesn't take place for the other table. To create an explicit transaction, begin the script with the BEGIN TRANSACTION statement. Any operations that follow will only be completed when the COMMIT TRANSACTION statement is issued.

Explicit transactions should be used whenever multiple modifications are dependent on each other. The chief advantage that explicit transactions bring to data modifications is that you can check for any errors in your operations prior to committing the transaction. If any errors are present you rollback the transaction. If no errors are detected, you commit the transactions. A common misconception is that transactions automatically supply this error detection. Nothing could be further from the truth.As an example, we will create a Savings account table and a Checking account table and then place a check constraint on the checking account table that enforces a minimum balance of $100.00. We will then populate the tables with data: Now that we have our two bank accounts set up, we will try to transfer$990.00 from our checking account to our savings account inside an explicit transaction:

The result of this transaction looks like this:

The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "AdventureWorks2008", table "dbo.MyChecking",column 'Amount'.

The message means that something unintended has happened. A query of the savings account and checking account table reveals an interesting outcome:

The checking account still has its original balance, but the savings account balance is now increased by \$990.00. This is because the update to the checking account was aborted when it violated the minimum balance constraint. However, because we did not do anything about the error, SQL Server continued with the next update and obediently modified the savings account balance, and then committed the transaction, just like we told it to. Good for us, bad for the bank. To prevent this from happening you must add error checking to your transactions.

Chapter (Creating and Managing Database Objects) covers error handling in greater detail, but for now it is important to see how error handling must be included in transactions to guarantee their consistency. Up to SQL Server 2000, there were very few options for effectively handling errors. This capability was improved considerably in SQL Server 2005 and of course in the current product version. The technique involves the use of TRY .. . CATCH blocks, similar to modern object-oriented programming languages. In T-SQL, TRY and CATCH are separate code blocks. The first code block starts with BEGIN TRY and concludes with END TRY. If this code fails for any reason, execution is set to the first line of code under the BEGIN CATCH statement and then execution continues until the END CATCH statement is reached. In essence, we are saying "Try to run this block of code. If it fails, catch the error and send it to the error-handling code block.” Code in the CATCH block runs only if an error is caught within the TRY block.

Executing the following script simply returns an error and does not alter either of the records. This is because an error is thrown in the TRY block and then caught in the CATCH block, causing the entire transaction to be aborted and rolled back:

You can also use the shorthand version of these statements, substituting TRAN for the word TRANSACTION:

BEGIN TRAN COMMIT TRAN ROLLBACK TRAN