Explicit Transactions

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 amount of data pages have been modified, it executes a checkpoint. This setting is adjustable but is beyond the scope of this book. It's 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 I 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. I will then populate the tables with data:

 CREATE TABLE MySavings (AccountNum Int NOT NULL, Amount Money NOT NULL) CREATE TABLE MyChecking (AcountNum Int NOT NULL, Amount Money NOT NULL) ALTER TABLE MyChecking ADD CONSTRAINT ckMinBalance CHECK (Amount > $100.00) INSERT MySavings VALUES (12345, $1000.00) INSERT MyChecking VALUES (12345, $1000.00) 

Now that I have my two bank accounts set up I will try to transfer $990.00 from my checking account to my savings account inside an explicit transaction:

 EGIN TRANSACTION  UPDATE MyChecking SET Amount = Amount - $990.00 WHERE AccountNum = 12345 UPDATE MySavings SET Amount = Amount + $990.00 WHERE AccountNum = 12345 COMMIT TRANSACTION  

The result of this transaction looks like this:

sg 547, Level 16, State 0, Line 2 The UPDATE statement conflicted with the CHECK constraint "ckMinBalance". The conflict occurred in database "AdventureWorks2000", table "MyChecking", column ‘Amount’. The statement has been terminated.     (1 row(s) affected)

The "1 row(s) affected" indicates that something unintended has happened. A query of the savings account and checking account table reveal an interesting outcome:

 ELECT Amount AS CheckingAmount FROM MyChecking WHERE AccountNum = 12345 SELECT Amount AS SavingsAmount FROM MySavings WHERE AccountNum = 12345     CheckingAmount --------------------- 1000.00     SavingsAmount --------------------- 1990.00

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 I did not do anything about the error SQL Server continued with the next update and obediently modified the savings account balance, then committed the transaction, just like I told it to. Good for me, bad for the bank. To prevent this from happening you must add error checking to your transactions.

Chapter 13 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. I will demonstrate two different error checking methods. The first is the SQL Server 2000 method and the second is the new improved SQL Server 2005 method:

 /*********************************************** *                                              * *       SQL SERVER 2000 Error Handling         * *                                              * ***********************************************/ BEGIN TRANSACTION  UPDATE MyChecking SET Amount = Amount - $990.00 WHERE AccountNum = 12345 IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION RETURN END ELSE UPDATE MySavings SET Amount = Amount + $990.00 WHERE AccountNum = 12345 IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION RETURN END ELSE COMMIT TRANSACTION /*********************************************** *                                              * *       SQL SERVER 2005 Error Handling         * *                                              * ***********************************************/ BEGIN TRANSACTION  BEGIN TRY UPDATE MyChecking SET Amount = Amount - $90.00 WHERE AccountNum = 12345 UPDATE MySavings SET Amount = Amount + $990.00 WHERE AccountNum = 12345 COMMIT TRANSACTION END TRY BEGIN CATCH RAISERROR(‘Transaction ROLLBACK TRANSACTION END CATCH 

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

 BEGIN TRAN COMMIT TRAN ROLLBACK TRAN