Rolling back transactions with cursors and local variables.

mattbowler, 2014-04-10

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:

cursor

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads