I’ve grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It’s a fairly human look at what’s involved in sailing on a Trident missile submarine…
I’ve been doing some work on a credit card payment system lately. Obviously this needs to be robust and consistent so I’ve been working on error handling and “transactionalising” certain parts of the process.
I needed to answer a couple of questions that I had along the way. What happens to local variables when a transaction is rolled back? What happens to an open cursor when a transaction is rolled back?
The first question came about because I was looking for a way to log errors if my transaction had been rolled back. Any logging outside of the transaction would not have access to the error details while logging to tables inside the transaction would be rolled back as well (you laugh? … I’ve seen this done!). What about using a local variable to hold interim values? Would they persist across a rollback? This was pretty quick and easy to test.
DECLARE @test INT SET @test = 1 BEGIN TRAN test SET @test += 10 ROLLBACK TRAN test SELECT @test --@test = 1 means the variable assignment is rolled back --@test = 11 means the variable assignment persists
This returns 11 for SQL 2005, 2008, 2008 R2 and 2012 (the += syntax wont work on 2005). Local variables are outside of the scope of the transaction. Intuitively this makes sense, there’s no real requirement for variables to have ACID properties – they’re only scoped to a particular session.
The next question arose because I needed to do a couple of things within an explicit transaction. There was a business requirement to only update a single row at a time so I chose to use a cursor within the transaction. I wrapped the logic in a try – catch block to rollback the transaction and handle any errors, and I wanted to know what would happen to the open cursor after a rollback?
Testing this I could use either CURSOR_STATUS() or sys.dm_exec_cursors() to check the status of my cursor. I stripped out all the code from my proc leaving just the control flow logic, used a simple query to populate the cursor, and faked out an error using raiserror().
DECLARE @db_name SYSNAME BEGIN TRAN test BEGIN TRY DECLARE test_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM sys.databases OPEN test_cursor FETCH NEXT FROM test_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM test_cursor INTO @db_name PRINT @db_name --create a fake error IF @db_name = 'tempdb' RAISERROR('BOOM',16,1) END CLOSE test_cursor DEALLOCATE test_cursor COMMIT TRAN test END TRY BEGIN CATCH IF XACT_STATE() <> 0 BEGIN ROLLBACK TRAN test; END --state of my cursor? SELECT CURSOR_STATUS('local','test_cursor') SELECT * FROM sys.dm_exec_cursors(@@SPID) END CATCH;
And on my system (SQL 2005, 2008, 2008R2 & 2012) I got:
Despite the cursor being declared and opened inside the transaction, it remains open after the transaction has rolled back. To me this was a little counterintuitive, but easy enough to deal with within the catch block:
IF EXISTS(SELECT 1 FROM sys.dm_exec_cursors(@@SPID)) BEGIN CLOSE test_cursor DEALLOCATE test_cursor END
IF CURSOR_STATUS('local','test_cursor') > -1 BEGIN CLOSE test_cursor DEALLOCATE test_cursor END
Happy transacting – but please clean up after yourself.