You can carry out transaction processing with Microsoft SQL Server in three ways:
Each of these methods is discussed in the following sections.
AutoCommit TransactionsAutoCommit transactions are the default transaction mode for SQL Server. Each individual Transact-SQL command automatically commits or rolls back its work at the end of its execution. Each SQL statement is considered to be its own transaction, with begin and end control points implied . [implied begin transaction] UPDATE account SET balance = balance + 1000 WHERE account_no = "123456789" [implied commit or rollback transaction] If an error is present within the execution of the statement, the action is undone (rolled back); if no errors occurred, the action is completed and the changes are saved. Now consider the banking transaction again and write the T-SQL statements to move money from the savings account to the checking account. Assume it was written as follows : declare @checking_account char(10), @savings_account char(10) select @checking_account = '0003456321', @savings_account = '0003456322' update account set balance = balance - 00 where account_number = @checking_account update savings_account set balance = balance + 00 where account_number = @savings_account What would happen if an error occurred updating the savings account? With AutoCommit, each statement is implicitly committed after it completes successfully, so the update for the checking account has already been committed. You would have no way of rolling it back except to write another separate update to add the $1,000 back to the account. If the system crashed during the updates, how would you know which if any completed, and whether you would need to undo any of the changes because the subsequent commands were not executed? You would need some way to group the two commands together as a single logical unit of work so they complete or fail as a whole. SQL Server provides transaction control statements that allow you to create multistatement user -defined transactions. Explicit User-Defined TransactionsTo have complete control of a transaction and define logical units of work that consist of multiple data modifications, you need to write explicit user-defined transactions. Any SQL Server user can make use of the transaction control statements; no special privileges are required. To start a multistatement transaction, use the BEGIN TRAN command, which optionally takes a transaction name : BEGIN TRAN[SACTION] [ transaction_name [WITH MARK [' description ']]] This name is essentially meaningless as far as transaction management is concerned , and if transactions are nested (which will be discussed later in this chapter), the name is only useful for the outermost BEGIN TRAN statement. Rolling back to any other name, besides a savepoint name, will generate an error and not roll back the transaction. The statements can only be rolled back when the outermost transaction is rolled back. Naming transactions is really only useful when using the WITH MARK option. If the WITH MARK option is specified, a transaction name must be specified. WITH MARK allows for restoring a transaction log backup to a named mark in the transaction log. (For more information on restoring database and log backups , see Chapter 16, "Database Backup and Restore.") This option allows you to restore a database to a known state, or to recover a set of related databases to a consistent state. However, be aware that BEGIN TRAN records are only written to the log if an actual data modification occurs within the transaction. A transaction is completed successfully by issuing either a COMMIT TRAN or COMMIT [WORK] statement, or can be undone using either ROLLBACK TRAN or ROLLBACK [WORK] . The syntax of these commands is as follows: COMMIT [TRAN[SACTION] [ transaction_name ]] [WORK] ROLLBACK [TRAN[SACTION] [ transaction_name savepointname ]] [WORK] The COMMIT statement marks the successful conclusion of the transaction. This statement can be coded as COMMIT , COMMIT WORK , or COMMIT TRAN . It makes no difference ”other than that the first two versions are SQL-92 ANSI compliant. The ROLLBACK statement unconditionally undoes all work done within the transaction. This statement can also be coded as ROLLBACK , ROLLBACK WORK , or ROLLBACK TRAN . The first two commands are ANSI-92 SQL compliant and do not accept user-defined transaction names . ROLLBACK TRAN is required if you want to roll back to a savepoint within a transaction. The following is an example of how you could code the previously mentioned banking example as a single transaction in SQL Server: declare @checking_account char(10), @savings_account char(10) select @checking_account = '0003456321', @savings_account = '0003456322' begin tran update account set balance = balance - 00 where account_number = @checking_account if @@error != 0 begin rollback tran return end update savings_account set balance = balance + 00 where account_number = @savings_account if @@error != 0 begin rollback tran return end commit tran There are certain commands that cannot be specified within a user-defined transaction, primarily because they cannot be effectively rolled back in the event of a failure. In most cases, because of their long-running nature, you would not want them to be specified within a transaction anyway. Here are the commands you cannot specify in a user-defined transaction:
SavepointsSavepoints allow you to set a marker in a transaction that you can roll back to undo a portion of the transaction, but commit the remainder of the transaction. The syntax is as follows: SAVE TRAN[SACTION] savepointname Savepoints are not ANSI-SQL 92 compliant, so you must use the SQL Server-specific transaction management commands that allow you to specify a named point within the transaction and then recover back to it. The following code illustrates the differences between the two types of syntax when using the SAVE TRAN command:
Note the difference between the SQL-92 syntax on the left and the SQL Server “specific syntax on the right. In the SQL-92 syntax, when you reach the ROLLBACK WORK command, the entire transaction is undone, rather than undoing only to the point marked by the savepoint. You have to use the SQL Server “specific ROLLBACK TRAN command and specify the savepoint name to roll back the work to the savepoint and still be able to subsequently roll back or commit the rest of the transaction. Nested TransactionsBy rule, you can't have more than one active transaction per user session within SQL Server. However, consider that you have a SQL batch that issues a BEGIN TRAN statement, and then subsequently invokes a stored procedure, which also issues a BEGIN TRAN statement. Because you can only have one transaction active, what does the BEGIN TRAN inside the stored procedure accomplish? In SQL Server, this leads to an interesting anomaly referred to as nested transactions. To determine whether transactions are open and how deep they are nested within a connection, you can use the global function called @@trancount . If no transaction is active, the transaction nesting level is . As a transaction is initiated, the transaction nesting level is incremented; as a transaction completes, the transaction nesting is decremented. The overall transaction remains open and can be entirely rolled back until the transaction nesting level returns to . You can use the @@trancount to monitor the current status of a transaction. For example, what would SQL Server do when encountering the following transaction (which produces an error because of the reference constraint on the titles table)? BEGIN TRAN DELETE FROM publishers WHERE pub_id = '0736' Is the transaction still active? You can find out using the @@trancount function: select @@trancount go ----------- 1 In this case, @@trancount returns a value of 1 , which indicates that the transaction is still open and in progress. This means that you can still issue commands within the transaction and commit the changes, or roll back the transaction. Also, if you were to log out of the user session from SQL Server before the transaction nesting level reached , SQL Server would automatically roll back the transaction. Although nothing can prevent you from coding a BEGIN TRAN within another BEGIN TRAN , doing so has no real benefit even though such cases might occur. However, if you nest transactions in this manner, you must execute a COMMIT statement for each BEGIN TRAN statement issued. This is because SQL Server modifies the @@trancount with each transaction statement and considers the transaction finished only when the transaction nesting level returns to . Table 31.1 shows the effects that transaction control statements have on @@trancount . Table 31.1. Transaction Statements' Effects on @@trancount
Following is a summary of how transactional control relates to the values reported by @@trancount :
Take a look at some sample code to show the values of @@trancount as the transaction progresses. This first example is a simple explicit transaction with a nested BEGIN TRAN :
Nested transactions are syntactic only. The only commit tran statement that has an impact on real data is the last one, the statement returning @@trancount to . That statement fully commits the work done by the initial transaction and the nested transactions. Until that final COMMIT TRAN is encountered , all of the work can be rolled back with a ROLLBACK statement. As a general rule, if a transaction is already active, you shouldn't issue another BEGIN TRAN statement. Check the value of @@trancount to determine if a transaction is already active. If you want to be able to roll back the work performed within a nested transaction without rolling back the entire transaction, set a savepoint instead of issuing a BEGIN TRAN statement. Later in this chapter, you will see an example of how to check @@trancount within a stored procedure to determine if the stored procedure is being invoked within a transaction and issuing a BEGIN TRAN or SAVE TRAN as appropriate. Implicit TransactionsAutoCommit transactions and explicit user-defined transactions in SQL Server are not ANSI-92 SQL compliant. The ANSI-92 SQL standard states that any data retrieval or modification statement issued should implicitly begin a multistatement transaction that remains in effect until an explicit ROLLBACK or COMMIT statement is issued. To enable implicit transactions for a connection, you need to turn on the IMPLICIT_TRANSACTIONS session setting. The syntax is as follows: SET IMPLICIT_TRANSACTIONS {ON OFF} After this option is turned on, transactions will be implicitly started, if not already in progress, whenever any of the following commands are executed:
Note that neither the ALTER VIEW nor ALTER PROCEDURE statement starts an implicit transaction. Implicit transactions must be explicitly completed by issuing a COMMIT or ROLLBACK , and a new transaction is started again on the execution of any of the preceding commands. If you plan to use implicit transactions, the main thing to be aware of is that locks are held until you explicitly commit the transaction. This can cause problems with concurrency and the ability of the system to back up the transaction log. Even when using implicit transactions, you can still issue the BEGIN TRAN statement and create transaction nesting. In this next example, IMPLICIT TRANSACTIONS ON has been turned on to see the effect this has on the value of @@trancount .
As you can see in this second example, if a BEGIN TRAN is issued while a transaction is still active, transaction nesting will occur and a second COMMIT is required to finish the transaction. The main difference is that a BEGIN TRAN was not required to start the transaction. The first INSERT statement initiated the transaction. When you are running in implicit transaction mode, you don't need to issue a BEGIN TRAN statement; in fact, you should avoid it to prevent transaction nesting and the need for multiple commits. Look at the previous banking transaction using implicit transactions: set implicit_transactions on go declare @checking_account char(10), @savings_account char(10) select @checking_account = '0003456321', @savings_account = '0003456322' update account set balance = balance - 00 where account_number = @checking_account if @@error != 0 begin rollback return end update savings_account set balance = balance + 00 where account_number = @savings_account if @@error != 0 begin rollback return end commit This example is nearly identical to the explicit transaction example except for the lack of a BEGIN TRAN statement. In addition, when in implicit transaction mode, you cannot roll back to a named transaction because no name is assigned when the transaction is invoked implicitly. You can, however, still set savepoints and roll back to savepoints to partially roll back work within an implicit transaction.
Implicit Transactions Versus Explicit TransactionsWhen would you want to use implicit transactions versus explicit transactions? If you are porting an application from another database environment that used an implicit transaction, that application will port over more easily with fewer code changes if you run in implicit transaction mode. Also, if the application you are developing needs to be ANSI-compliant and run across multiple database platforms with minimal code changes, you might want to use implicit transactions. If you use implicit transactions in your applications, just be sure to issue COMMIT statements as frequently as possible to prevent leaving transactions open and holding locks for an extended period of time, which can have an adverse impact on concurrency and overall system performance. If your application is only going to be hosted on SQL Server, it is recommended that you use AutoCommit and explicit transactions so that changes are committed as quickly as possible and only those logical units of work that are explicitly defined will contain multiple commands within a transaction. |