Savepoints: The Other Other White Meat

Most T-SQL guides and tutorials I've seen concentrate on the following transaction commands: BEGIN, COMMIT, and ROLLBACK, which I'll henceforth refer to as the "big three". While they're certainly very important, there's also a fourth command, SAVE, which is typically treated like a distant cousin who's only seen at family reunions every few years. It's a shame because while SAVE is not always necessary, it can be a huge help when you need it.

If you've ever found yourself in a situation where you have a multi-step process that takes a long time to complete, you're probably using the big three transaction commands to ensure the process is atomic – that everything succeeds or everything fails. Now let's say that somewhere in the middle of your process one of those steps fails. Your error handling code detects the failure and the transaction is rolled back as if nothing ever happened. This is an acceptable outcome, however all of the time that went into steps of the process that succeeded was for nought. What would really be awesome is a way to rollback only part of a transaction, so all the work that did succeed isn't lost and your process can attempt to continue. This is exactly where savepoints come in.

What They Do

When you set a savepoint using the SAVE TRANSACTION command, a marker is created within the transaction. Should something go sour later on and you don't want to rollback the entire transaction, you can rollback just to the savepoint. Everything that occurred before the savepoint will still be there, whereas everything that happened after it will be un-done. Once you rollback to a savepoint, you'll have the same transactional options you had before: committing the transaction as-is, doing a rollback of the entire transaction, or performing more operations within the transaction and committing (or rolling back) when done.

I should emphasize again that the transaction remains open when rolling back to a savepoint. Savepoints will allow you to get back to a point during a transaction so that you can re-try a failed step (perhaps due to a deadlock or network error) but you still need to complete the transaction by either committing or rolling it all back. If you do a rollback to a savepoint and then forget about it, sooner or later you'll be trying to figure out why your transaction log has grown so much (and it will be due to an open transaction!)

At this point you may be wondering why you can't just create a transaction within a transaction and accomplish the same thing by rolling back the inner transaction. You can try, but it won't work. While it is syntactically possible to create nested transactions, committing a nested transaction does nothing, and rolling one back will not produce the desired effect. I would prove this with a demo, however Paul Randal (blog | twitter) has already done an excellent job of that and I see no sense in duplicating his effort. Check out his blog post if you want to learn more.

How To Use Them

The instructions from Books Online show that the SAVE TRANSACTION command is rather simple. The only argument (which is required) is a name for the savepoint of up to 32 characters in length. You can create multiple savepoints per transaction and can rollback to any of them provided they have different names. If you create a savepoint with a name that's already in use, the older one will be overwritten and only the newer one will be accessible.

To rollback to a savepoint, specify the savepoint name in the ROLLBACK TRANSACTION statement. Omitting the savepoint name parameter means it's just a plain old rollback command and the entire transaction will be rolled back just as one would expect. Let's try it out with some sample code:

 1-- ** Don't run this all at once!! **
 2-- You'll want to run these statements one at a time, checking the
 3--  contents of table 'tbl' in-between each statement!
 4
 5-- Do this in AdventureWorks (or any other dev db) for safety
 6USE AdventureWorks2008;
 7
 8-- create table and add some data
 9CREATE TABLE tbl (
10  id INT PRIMARY KEY CLUSTERED,
11  val VARCHAR(20)
12);
13INSERT INTO tbl VALUES (1,'foo');
14INSERT INTO tbl VALUES (2,'foofoo');
15INSERT INTO tbl VALUES (3,'foobar');
16INSERT INTO tbl VALUES (4,'bar');
17
18-- begin our transaction and add another row
19BEGIN TRAN;
20INSERT INTO tbl VALUES (5,'fubar');
21-- if you select * from tbl, you should now see 5 rows
22
23-- set a savepoint named "Savept"
24SAVE TRAN Savept;
25
26-- delete a record (oops!)
27DELETE FROM tbl WHERE id = 3;
28-- looking at tbl, you'll now see rows 1,2,4,5
29
30-- rollback to the savepoint
31ROLLBACK TRAN Savept;
32-- tbl will now have 5 rows again, just like it did at the savepoint
33
34-- roll it all back
35ROLLBACK TRAN;
36-- tbl will have 4 rows now just like before the transaction
37
38DROP TABLE tbl;
39-- clean up our mess :)

Convinced it's nifty? Awesome! :) One final point I'd like to touch on is locking. Books Online for the SAVE TRANSACTION command emphasizes the fact that locks acquired during a transaction are held until the transaction completes. So what happens when you rollback to a savepoint? Any locks acquired after the savepoint will be released, just like all locks acquired by a transaction are released when the entire transaction is rolled back.

To see this for yourself, run through the above sample code again and open another query window to check the locks held by that process using sp_lock or the sys.dm_tran_locks DMV. You'll notice that the locks associated with deleting row 3 disappear when rolling back to the savepoint. A notable exception to this behavior is if lock is escalated or converted during the transaction. If that is the case, the lock will not revert to its previous state, and it will not be released until the transaction commits or is rolled back.

So there you have savepoints. They aren't always necessary, but if you're really in a pinch they sure can come in handy!