Enforcing Procedural Business Logic Using Transactions

• Enforce procedural business logic by using transactions.

  • Design and manage transactions.

  • Manage control of flow.

Each statement that accesses a database is enclosed in a transaction. A transaction implies several things:

• Atomicity. A transaction cannot be broken up into smaller units; either the entire transaction happens or none of it happens.

• Consistency. A completed transaction leaves the database in a consistent state.

• Isolation. A transaction is isolated from other transactions in the database, so transactions can't overwrite each other's data. Transactions, in other words, can't interfere with other transactions that are running concurrently.

• Durability. A transaction, after it has been applied, sticks in the database.

These qualities are easy to recall by remembering the word "ACID": Atomicity, Consistency, Isolation, and Durability. SQL Server provides the atomicity, isolation, and durability for you, but it's up to you to make sure that a transaction leaves the database in a consistent state. Atomicity and durability are handled by the transaction logging system and, to an extent, by the lazy writer, which was covered back in Chapter 2, "Data Modeling." Isolation is handled by the lock manager, which is covered in this chapter.

Designing and Managing Transactions

• Design and manage transactions.

If you don't explicitly tell SQL Server to treat a group of statements as a transaction, it implicitly puts each statement in its own transaction. For the purposes of an implicit transaction, the only statements that really count are the statements that interact with a database: SELECT , INSERT , UPDATE , and DELETE .

To explicitly put a group of statements into a transaction, you can use the BEGIN TRANSACTION command. This command tells SQL Server all commands that follow up until the end of the transaction, which is noted with an COMMIT TRANSACTION . In the event of a problem with the data being manipulated, you can also call ROLLBACK TRANSACTION . If there is an error during the execution of the transaction, such as a server shutdown, a disk error of some type, or lock contention , then the transaction automatically rolls back.

Here's an example to help you understand why transactions need to be used. Let's say you have a bank database, and it has a Deposit table and a Withdrawal table. Somebody wants to transfer funds from one account, account number 42, into another account, account number 64. So here's how that would look:

 INSERT INTO Deposit VALUES (64, 100.00) INSERT INTO Withdrawal VALUES (42, 100.00) 

That looks fine. However, the server crashed between the two INSERT statements. When the administrator brings it back up, there's been a deposit for $100.00 into one account, but no withdrawal to match, so the SQL Server just invented $100.00.

To do this correctly, these statements should be enclosed in an explicit transaction, like this:

 BEGIN TRANSACTION         INSERT INTO Deposit VALUES (64, 100.00)         INSERT INTO Withdrawal VALUES (42, 100.00) COMMIT TRANSACTION 

Now if the server crashes when the transaction is halfway complete, SQL Server rolls back the transaction automatically so that no money is created out of thin air and you may not lose your job as quickly.

Managing Control of Flow with Transactions

• Manage control of flow.

SQL Server also has the capability to create nested transactions, where transactions are inside other transactions. In that case, the global function @@TRANCOUNT contains the current nesting level of transactions, which is basically the number of times you've executed a BEGIN TRANSACTION statement. You'll cover that a bit more later in this chapter in the section, "Raising Errors."

You need to be aware of a few rules about transactions.

• Some statements are not allowed inside an explicit transaction. These are CREATE DATABASE , ALTER DATABASE , DROP DATABASE , BACKUP DATABASE , RESTORE DATABASE , BACKUP LOG , RESTORE LOG , RECONFIGURE , and UPDATE STATISTICS .

• If you have nested transactions (transactions started within other transactions) and you call ROLLBACK TRANSACTION , it rolls back all the transactions currently pending.

• The COMMMIT WORK statement is functionally equivalent to the COMMIT TRANSACTION statement. The ROLLBACK WORK statement is functionally equivalent to the ROLLBACK TRANSACTION statement. These can all be abbreviated down to just COMMIT and ROLLBACK . You can also just abbreviate the word TRANSACTION to TRAN , such as COMMIT TRAN , and so on.

Two other complications need to be thrown into this already semi-confusing pile: naming transactions and savepoints. Any time you execute a BEGIN TRANSACTION , you can tell SQL Server to assign a name to the transaction. The name for a BEGIN TRANSACTION is fairly superfluous, however, because any rollback still rolls back all pending transactions. For example:

 BEGIN TRANSACTION OuterTransaction         INSERT INTO Deposit VALUES (42, 100.00)         BEGIN TRANSACTION InnerTransaction         INSERT INTO Withdrawal VALUES (100, 37.50)         ROLLBACK TRANSACTION 

The final ROLLBACK TRANSACTION is still going to roll back all the way to OuterTransaction . As mentioned, this just adds to the complication.

EXAM TIP

Test Question There's almost guaranteed to be a question on your test about a rollback with a named transaction and how much does it roll back. Just remember: Unless there's a savepoint involved, a rollback goes all the way back to the first BEGIN TRANSACTION that wasn't followed by a COMMIT TRANSACTION .

You can create a savepoint within a transaction and roll back to that savepoint without rolling back the entire transaction. To create a savepoint within a transaction, use the SAVE TRANSACTION command and name the savepoint. Then when you want to roll back to the savepoint, you can name the savepoint. The syntax is identical to a named transaction, just in case you weren't stupefied by the complexity yet. Here's an example:

 BEGIN TRANSACTION OuterTransaction         INSERT INTO Deposit VALUES (42, 100.00)         SAVE TRANSACTION SavePoint1         INSERT INTO Withdrawal VALUES (100, 37.50)         ROLLBACK TRANSACTION SavePoint1 --rolls back to the save point 

If that ROLLBACK TRANSACTION didn't have the savepoint name out there, it would roll back to the beginning of the transaction, just as you'd expect.

IN THE FIELD: SCARED OF TRANSACTIONS?

This can all be a bit overwhelming. Just to lower the fear factor a little bit, outside of this exam, it's extremely unlikely you'll ever see a named transaction or a savepoint. That's because just as it looks really complicated, it's also actually really complicated. Nobody, including experienced SQL Server administrators, uses savepoints except when those experienced administrators try to trip up other administrators over minutiae while hanging out at the local drinking establishment.

You've got to learn it for the exam, because as that exam tip said, there's almost certain to be at least one question on it. Other than that, don't let it get you concerned that you're going to be consumed by some huge, ugly 30-nesting-levels-deep transaction with fourteen distinct savepoints. At least, not on one of this author's databases.

Raising User -Defined Errors

This section concentrates on one statement: RAISERROR . This is the statement you can use in your batches, and later in your stored procedures, to handle things such as data exceptions.

NOTE

A Little Spelling Issue Note the lack of two e 's in the middle of RAISERROR . That's not a typographical error; it's the designers of Transact -SQL being cute. Don't let that trip you up as you work through the examples in this chapter.

System and User Error Messages

This section talks about what error messages really are, how to use them, and how to apply error handling in your transactions.

An error message has three distinct properties: an error number, an error severity, and the message text. The error number is a number that identifies the error message, specifically in the sysmessages table. The error severity is the status of the server during the error message. A user can raise errors with severities ranging from 1 to 16, and system administrators and the system itself can raise error messages from 17 to 25. The higher the severity level, the more severe the problem. Severity codes up to 10 are for informational messages; codes from 11 through 16 are error messages for users, such as " invalid object name " or " incorrect syntax near ',' ". The higher severity codes are for things such as data corruption and hardware failure.

There's also a fourth element that goes with error messages, and it's the mystical and strange state parameter . It's not used by SQL Server any more, but it's required whenever you raise an error. Just think of it as a free way to put information into the error log. It's just some number, ranging from 1 to 127. It doesn't matter which one you use ”by convention it's generally left at 1. You do, however, have to have a state.

If you are writing an application or a script that needs to raise certain exceptions periodically, such as a data integrity problem, you can actually add a message into the sysmessages table to standardize the error. This is a great way to handle internationalization problems, because you can enter the same message with the same message number in several different languages, and SQL Server automatically picks the correct language for the user. This can be done using the sp_addmessage system-stored procedure.

The sysmessages table and the error handling facilities in SQL Server are very versatile. They enable you to insert tags into the message that can be replaced with specific information, such as table names and variable names , so that the error message can be customized to fit the specific situation.

Here's an example error message that could be created: " Data Error: Attempt to remove manager from group %s would leave the group without a manager. " The %s in there should look familiar to C programmers; it's the tag used to denote that a string will be entered in that place. Later, when you write code that raises the error, you can specify the text to be added into the message.

Now, how do you raise an error?

Raising Errors

Raising an error involves using the aforementioned RAISERROR statement. Here's an example of that statement:

 RAISERROR ('Unable to continue processing, insufficient data', 10, 1) with log 

Now you have the error message itself, the state (which is required in the command but is just an arbitrarily chosen number), and the phrase " with log ," which tells SQL Server to put an event into the SQL Server error log as well as the Windows NT Event Log. Note the location of the parentheses in the statement. They are required. If you execute that, and then check your Windows NT event log, you'll see an informational message in the log from MSSQLSERVER.

Here's how you do one of those snazzy inline-replacement error messages described earlier:

 RAISERROR ('Unable to continue processing, insufficient data in %s.', 10, 1, 'Job Table') 

This prints out the error message " Unable to continue processing, insufficient data in Job table ." You can add that into your sysmessages table this way:

 sp_addmessage 50001, 10, 'Unable to continue processing, insufficient data in %s.' 

And then you can execute something like this:

 RAISERROR (50001, 10, 1, 'Job Table') with log 

This returns the same message as before. You could then add the message in a few different languages, and SQL Server would automatically respond back to the user in the language requested . You do that with the @lang parameter to sp_addmessage , which works like this:

 sp_addmessage 50001, 10, 'Something bad happened in Spanish %s.', 'Spanish'