- What is a Transaction? A transaction is a set of operations to be completed at one time, as though they were a single operation. A transaction must be fully completed or not performed at all. All SQL statements are inherently transactions, from grant and create statements to the data-modification statements insert, update, and delete. Consider the following update example: update titles set price = price * 1.02 This statement modifies all rows in the titles table. SQL Server guarantees that, regardless of the size of the titles table, all rows will be processed or no rows will be processed at all. What if half the rows are modified and the server fails? When the server comes back up (but before the database is available for use), it rolls back the incomplete transaction, recording the rollback in the transaction log. This is all part of an automatic recovery process that takes place on each database every time the server is restarted. SQL Server also includes transaction-control syntax to group sets of SQL statements together into single logical work units: . begin transaction starts a unit of work . commit transaction completes a unit of work . rollback transaction cancels a unit of work The following example consists of two separate statements - one that enters an order and another that depletes inventory -In a single transaction: begin transaction update inventory set in_stock = in_stock - 5 where item_num = "14141" insert orders (cust_num, item_num, qty) values ("ABC151", "14141", 5) commit transaction Because the two data-modification statements are wrapped inside a begin transaction ... commit transaction structure, SQL Server guarantees that the inventory won't change unless the order is also entered. Look at the same example, but with the rollback transaction statement instead of commit: begin transaction update inventory set in_stock = in_stock - 5 where item_num = "14141" insert orders (cust_num, item_num, qty) values ("ABC151", "14141", 5) rollback transaction When the server encounters the rollback statement, it discards all changes in the transaction and returns the data to the state it was in before work began.
- Transaction Programming The programming issues associated with writing transactional SQL are fairly straightforward. After you issue a begin transaction statement, the server performs all the subsequent work without formally writing a final record of the work. At any time after issuing a begin transaction statement, you can roll back the entire transaction or commit it. The SQL Server implicitly commits work executed outside of explicit transactional control. For example, this set of statements includes no explicit transactional syntax: insert publishers (pub_id, pub_name, city, state) values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") update titles set pub_id = "1111" where pub_id = "1234" delete authors where state = "CA" Each of these statements is treated as its own transaction. The server would treat the preceding batch like this: [implicit BEGIN TRANSACTION] insert publishers (pub_id, pub_name, city, state) values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") [implicit COMMIT TRANSACTION] [implicit BEGIN TRANSACTION] update titles set pub_id = "1111" where pub_id = "1234" [implicit COMMIT TRANSACTION] [implicit BEGIN TRANSACTION] delete authors where state = "CA" [implicit COMMIT TRANSACTION] * Each statement is guaranteed to be carried to completion or rolled back to the beginning.
- Transactions and batches In the preceding example, you may have wanted the whole operation to complete or do nothing at all. The obvious is to wrap the entire operation in a single transaction, like this: begin transaction insert publishers (pub_id, pub_name, city, state) values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") update titles set pub_id = "1111" where pub_id = "1234" delete authors where state = "CA" commit transaction In general, you want to enclose each transaction in a single batch, using conditional programming constructs to handle special cases. begin transaction insert publishers (pub_id, pub_name, city, state) values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") if @@error = 0 print "publishers insert was successful. Continuing." update titles set pub_id = "1111" where pub_id = "1234" delete authors where state = "CA" commit transaction end else begin print "publisher insert failed. rolling back transaction" rollback transaction end
- Savepoints SQL Server enables you to mark a savepoint in a transaction. Savepoints let you to do some work inside a transaction and then roll back just that work, based on circumstances. In this example, the program sells several items and then tests inventory (which is automatically updated through a trigger) and rolls back a portion of the work if inventory is insufficient: begin tran save tran item111 /* mark a savepoint before each insert */ insert order (ord_no, item_no, qty) values ("2345", "111", 15) if (select in_stock from inventory where item_no = '111') < 0 begin rollback tran item111 /* roll back just this item if incorrect */ print "Item 111 would be back-ordered, cancelling order" end save tran item999 /* mark a savepoint before each insert */ insert order (ord_no, item_no, qty) values ("2345", "999", 5) if (select in_stock from inventory where item_no = '999') < 0 begin rollback tran item999 /* roll back just this item if incorrect */ print "Item 999 would be back-ordered, cancelling order" end save tran item444 /* mark a savepoint before each insert */ insert order (ord_no, item_no, qty) values ("2345", "444", 25) if (select in_stock from inventory where item_no = '444') < 0 begin rollback tran item444 /* roll back just this item if incorrect */ print "Item 444 would be back-ordered, cancelling order" end commit tran By wrapping the inserts in a transaction, the programmer gets two benefits. First, the overall effect of each insert can be observed before the work is committed, which makes it easy to reverse the effects of problem updates. The second benefit is that all the legitimate inserts included in the batch go in; the rest are rejected. The scope of transaction savepoint names is local, so you don't have to worry about generating unique names for your savepoints. Savepoints and related rollback statements don't affect program flow. Rolling back to a savepoint allows processing to continue forward from that point.
- Nested Transactions It's important to note that SQL Server doesn't implicitly commit work when you log out. For example, what if you issue the following statement and then log out? begin transaction insert publishers (pub_id, pub_name, city, state) values ("1111", "Joe and Mary's Books", "Northern Plains", "IA") Any uncommitted transactions are rolled back automatically when you log out. The transaction nesting level can be retrieved for your connection with the global variable @@trancount. You need to understand how each transactional statement affects @@trancount to write properly nested transactions and to manage transactions through triggers and stored procedures. Statement Effect on @@trancount ----------------------------------------------------------------------- begin transaction @@trancount = @@trancount + 1 commit transaction @@trancount = @@trancount - 1 save transaction (no effect) rollback transaction @@trancount = 0 rollback transaction save_name (no effect) ------------------------------------------------------------------------ Take a look at how the server handles transaction nesting. In this example, transactions are nested two levels deep: begin tran update titles set price = price * 1.1 from titles where pub_id = "1234" begin tran update titles set advance = advance * 1.5 where pub_id in (select pub_id from publishers where state = "MA") commit tran delete titles where type = "UNDECIDED" commit tran Let's track @@trancount through each statement. Assuming that @@trancount starts out at zero: Statement Effect on @@trancount ------------------------------------------------------------------------- begin tran @@trancount = @@trancount + 1 = 1 update titles @@trancount = 1 begin tran @@trancount = @@trancount + 1 = 2 update titles @@trancount = 2 commit tran @@trancount = @@trancount - 1 = 1 delete titles @@trancount = 1 commit tran @@trancount = @@trancount - 1 = 0 -------------------------------------------------------------------------- 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 0, which forces physical data to be written to disk.
- Transactions and Locking To ensure data integrity, SQL Server places exclusive locks on the pages modified during a transaction. In the previous example, pages in both the titles and publishers tables are involved. As SQL Server progresses through the query, locks are acquired on each table modified by a statement and are then held until SQL Server reaches a commit tran that sets @@trancount to 0 (or rollback tran). A long-running transaction performing data modifications to many different tables can effectively block the work of all other users in the system. Because of this possibility, you need to be aware of the performance and concurrency issues involved with writing transactions. When writing transactional code, keep these things in mind: . Keep transactions as short as your application allows. . Avoid returning data with a select in the middle of a transaction. . Try to write all transactions within stored procedures. . Avoid transactions that span multiple batches.
- Transactions and Triggers Triggers considered part of the transaction in which a data modification is executed. In this example, an update trigger on the titles table fires as part of the transaction: begin tran update titles set price = $99 where title_id = "BU1234" commit tran
- Transactions and Stored Procedures Writing all your transactions in stored procedures can provide better performance by avoiding partial transactions, especially because the server provides error messages to help you manage the transaction nesting level within procedures. The biggest concern you have is how to handle rollback transaction statements within stored procedures. Mistakes in using rollback statements in procedures can result in data-integrity problems. Consider this stored procedure, which inserts a row into a table, tests the table after the insert, and rolls back the insert if more than three rows exist with that value: /* stored procedure example coded improperly (see below) */ create proc p2 (@parm int) as begin tran insert tally_table (c1) values (@parm) if (select count (*) from tally_table where c1 = @parm) > 3 begin raiserror 99997 "too many rows with that value - rolling back " rollback tran return 99997 /* error ... rolled back */ end else begin commit tran return 0 /* no error */ end If you execute the stored procedure when the table contains only two rows with c1 = 1, the procedure runs properly and the insert is entered: 1> exec p2 1 2> go 1> select * from tally_table 2> go c1 -------------- 1 1 1 (3 rows affected) When you run the procedure again, the rollback statement executes, and you receive the following error message: 1> exec p2 1 2> go Msg 99997 , Level 16, State 1: too many rows with that value - rolling back 1> select * from tally_table 2> go c1 ------------- 1 1 1 (3 rows affected) -------------- As expected, the procedure works properly and the table contains only three rows after the insert fails. The problem with this procedure arises only when it's executed from within a transaction. Consider this example: begin tran exec p2 1 exec p2 2 commit tran What you intended by writing this as a transaction was that the inserts with value 2 would not occur unless you could make the related insert with value 1; both inserts should go in as a unit or no inserts should take place. Let's look at the output when the table contains three rows with value 1: 1> select * from tally_table 2> go c1 ------------- 1 1 1 (3 rows affected) -------------- 1>begin tran 2> exec p2 1 3> exec p2 2 4> commit tran 5>go Msg 99997, Level 16, State 1: too many rows with that value - rolling back Msg 266, level 16, State 1: transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0. Msg 3902, Level 16, State 1: The commit transaction request has no corresponding BEGIN TRANSACTION. 1> select * from tally_table 2> go c1 ------------- 1 1 1 2 (4 rows affected) -------------- Before the batch, the table contains three rows; afterward, it contains four rows. The new row contains the value 2, so only half of the transaction was executed. The integrity of the transaction has been lost. This error occurs because the transaction nesting level (that is, the value of @@trancount) when the procedure starts is different from when it ends. Why? rollback transaction returns @@trancount to 0 regardless of the prior transaction nesting level. Clearly, this is a problem if the calling batch is unaware of the nesting level. See Table 9.3 (page 295). There are several methods of coding stored procedures with transactions to ensure that the procedure works properly as a stand-alone transaction or as a part of a larger, nested transaction. Here is one example of how to write a stored procedure with transaction control: /* proc to demonstrate no net change to @@trancount ** while still rolling back changes within the proc ** VERY IMPORTANT: return an error code ** to tell the calling procedure rollback occurred */ create proc p1 as declare @trncnt int select @trncnt = @@trancount --save @@trancount value if @trncont = 0 -- transaction has not begun begin tran p1 -- begin tran increments nest level to 1 else save tran p1 -- save tran doesn't increment nest level /* do some processing */ if (@@transtate = 2) -- or other error condition begin rollback tran p1 -- rollback to savepoint, or begin tran return 25 -- return error code indicating rollback end /* more processing if required */ if @trncnt = 0 -- this proc issued begin tran commit tran p1 -- commit tran, decrement @@trancount to 0 -- commit not required with save tran return 0 /* successful return */ As important as it is to write the stored procedure properly, it's equally important to write the batch calling the procedure to make proper use of the information provided by the stored proc return codes. Here is an example of how to manage nested procedures from the calling batch: /* Retrieve status code to determine if proc was successful */ declare @status_val int, @trncnt int select @trncnt = @@trancount --save @@trancount value if @trncont = 0 -- transaction has not begun begin tran t1 -- begin tran increments nest level to 1 else -- otherwise, already in a transaction save tran t1 -- save tran doesn't increment nest level /* do some processing if required */ if (@@transtate = 2) -- or other error condition begin rollback tran t1 -- rollback to savepoint, or begin tran return -- and exit batch/procedurere end execute @status_val = p1 --exec procedure, begin nesting if @status_val = 25 -- if proc performed rollback begin -- determine whether to rollback or continue rollback tran t1 return end /* more processing if required */ if @trncnt = 0 -- this proc/batch issued begin tran commit tran t1 -- commit tran, decrement @@trancount to 0 return -- commit not required with save tran * The important point is that stored procedures and the batches that call those procedures need to be consistent with each other to provide proper data-integrity control.
- Long-Running Transactions There is no specific definition of a long-running transaction, but as transactions get longer, problems arise. Long-running transactions do stress elements of the system that otherwise run quite well. In particular, you may encounter performance or concurrency problems because of issues related to the transaction log, the caching system, and locking. Some of the symptoms of long-running transactions include the following: . Your transaction log fills up. . You're holding blocking locks that prevent all work by other users from continuing. . Ordinarily, all transactional work is performed in memory. Only when a commit is executed are the changes to the transaction log flushed to disk; changes to tables and indexes wait until a system-initiated checkpoint. If a transaction gets too long, the caching system may run out of unused memory and initiate a checkpoint to release additonal memory. This action could slow down your transaction as well, and a rollback following a checkpoint is far more disk-intensive than one is executed only in memory. * Reducing the size of your transaction is usually helpful when you start to encounter performance or blocking problems. * Reducing the number of indexes on a table also reduces the number of log writes required to handle complex modifications.
Last update February 8, 1998.