Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Rolling back transactions with cursors and local variables.

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.


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...