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

Or:

IF CURSOR_STATUS('local','test_cursor') > -1
BEGIN
CLOSE test_cursor
DEALLOCATE test_cursor
END

Happy transacting – but please clean up after yourself.