Understanding nested transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Understanding nested transactions

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2009-12-20 : 16:01:11

Hi,

I'm trying to better understand nested transactions before using them in a project.

I have the following scenario:
-Table 1
-Table 2

I have an SProc to archive data from Table 2 and the SProc to archive the data in Table 1 needs to archive Table 1 first but if it fails, I need it to roll back the changes to Table 2 as well.

What's got me confused however is I've setup the following testbed and when the second call to the child SProc fails, it doesn't look like it can be rolled back.

Could someone help explain it to me please?

Many thanks in advance.

Tim

CREATE TABLE TimsTest(
	Id int
CONSTRAINT PK_TimsTest PRIMARY KEY CLUSTERED 
(
	Id ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE TimsTestProc
AS

BEGIN

DECLARE @TranName nvarchar(10)
SET @TranName = 'IntTran'

BEGIN TRAN @TranName
	
	INSERT INTO TimsTest VALUES (1)

	IF @@error <> 0
	  BEGIN
		ROLLBACK TRAN @TranName
		RETURN
	  END
	ELSE
	  BEGIN
		COMMIT TRAN @TranName
		RETURN
	  END
END
GO

CREATE PROCEDURE TimsTestProcError
AS

BEGIN

	RAISERROR('This is just a test', 1, 1)

END
GO

--DELETE FROM TimsTest

DECLARE @TranName nvarchar(10)
SET @TranName = 'TimsTestTran'

BEGIN TRAN @TranName

	EXEC TimsTestProc
	
	INSERT INTO TimsTest VALUES (2)

	SELECT COUNT(*) FROM TimsTest
	
	EXEC TimsTestProc

	IF @@error <> 0
	  BEGIN
		ROLLBACK TRAN @TranName
		RETURN
	  END

	EXEC TimsTestProcError
	
	IF @@error <> 0
	  BEGIN
		ROLLBACK TRAN @TranName
		RETURN
	  END

COMMIT TRAN @TranName

SELECT * FROM TimsTest

DROP TABLE TimsTest
DROP PROCEDURE TimsTestProc
DROP PROCEDURE TimsTestProcError

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

timgaunt
Posting Yak Master

115 Posts

Posted - 2009-12-23 : 09:06:41

Does no-one really know the answer to this? I can't beleive no-ones needed to do this before...?

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-23 : 10:01:13

I didn't respond because I was confused by your question/explaination. I don't see why you need a nested transaction. Since you describe wanting to an "all or nothing" scenario then you just need a single transaction. let your calling procedure control the transaction. Remove the explicit transaction from the called procedure. Perhaps return (to the calling procedure) a non-zero return code if you want to rollback. Also, since you are using 2005 I would use try/catch blocks as opposed to checking @@error.

Be One with the Optimizer
TG

timgaunt
Posting Yak Master

115 Posts

Posted - 2009-12-24 : 07:52:49

Hi TG,

Thanks, sorry for the confusion, I wasn't sure how to explain it. The reason I will have a nested transaction is because the two procedures can be called independantly from the UI. In my example SProc above the user can call either TimsTestProc, TimsTestProcError or just run the SProc that calls both TimsTestProc and TimsTestProcError in order.

I'll swap over to Try/Catch's, thanks for that.

Tim

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

timgaunt
Posting Yak Master

115 Posts

Posted - 2009-12-24 : 08:01:57

Brilliant, looks like Try/Catch blocks solved my troubles anyway.

Thanks TG.

For others, once I rewrote the above script to the following:


CREATE TABLE TimsTest(
	Id int
CONSTRAINT PK_TimsTest PRIMARY KEY CLUSTERED 
(
	Id ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE TimsTestProc
AS

BEGIN

DECLARE @TranName nvarchar(10)
SET @TranName = 'IntTran'

BEGIN TRAN @TranName

	BEGIN TRY
		INSERT INTO TimsTest VALUES (1)
		COMMIT TRAN @TranName
		RETURN
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN @TranName
		RETURN
	END CATCH

PRINT 'After SProc Try/Catch Blocks'

END
GO

CREATE PROCEDURE TimsTestProcError
AS

BEGIN

	RAISERROR('This is just a test', 1, 1)

END
GO

--DELETE FROM TimsTest

DECLARE @TranName nvarchar(10)
SET @TranName = 'TimsTestTran'

BEGIN TRAN @TranName

	BEGIN TRY
		EXEC TimsTestProc
		
		INSERT INTO TimsTest VALUES (2)

		SELECT COUNT(*) As [RecordCount] FROM TimsTest
		
		EXEC TimsTestProc

		EXEC TimsTestProcError
		
		COMMIT TRAN @TranName
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN @TranName
	END CATCH

PRINT 'After Try/Catch Blocks'

SELECT * FROM TimsTest

DROP TABLE TimsTest
DROP PROCEDURE TimsTestProc
DROP PROCEDURE TimsTestProcError

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-12-24 : 10:15:34

Just so people understand the nature of nested transactions, for all inner transactions the transaction name is ignored by sql server and the only affect of BEGIN/COMMIT/ROLLBACK commands are to increment/decrement the transaction count. Only when the count is 1 does a commit or rollback affect the database.

So in the example above, since TimsTestProc is called from within a transaction the (nested) transaction contained in TimsTestProc is meaningless.

SAVE TRANSACTION can mark a point in the log where a subsequent rollback will not undo. But at that point the entire transaction still needs to be committed or rolled back.

Tim's test doesn't really need the error proc because the primary key prevents the second call of TimsTestProc from completing successfully. To illustrate that point as well as SAVE TRAN try replacing the original code block with this:

try running with and without the SAVE TRAN to see the difference.


DECLARE @TranName nvarchar(10)
SET @TranName = 'TimsTestTran'

BEGIN TRAN @TranName
	BEGIN TRY

		EXEC TimsTestProc
              save transaction @tranname
		EXEC TimsTestProc
	
		COMMIT TRAN @TranName
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN @TranName
	END CATCH

PRINT 'After Try/Catch Blocks'
SELECT * FROM TimsTest

Be One with the Optimizer
TG

timgaunt
Posting Yak Master

115 Posts

Posted - 2009-12-24 : 10:55:26

Interesting comparison thanks TG. The reason for TimsTestProcError was just to test different errors as when you RAISEERROR with a severity of 10 or less, it doesn't trigger the try/catch block.

Interesting findings thanks.

Tim

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Chayan
Starting Member

2 Posts

Posted - 2010-04-08 : 08:35:18

Hi Tim/TG,

The discussion was intersting.

However, i am not convinced that the 2nd approach using TRY..CATCH
actually solved the issue. please correct me if I am wrong.

In SQL Server:-
- A ROLLBACK at any level of a nested transaction will rollback till the outermost transaction.
- You cannot use a ROLLBACK with the TRANSACTION NAME in an INNER TRANSACTION.

In your 2nd approach, every thing is working smooth, because when the insert error happens in the inner transaction, control passes to the CATCH block of INNER transaction. The CATCH block there attempts to ROLLBACK using the INNER TRANSACTION NAME, that will raise another error.

So the ROLLBACK doesnot actually happens in the INNER transaction and the ROLLBACK ERROR is trapped by the OUTER TRANSACTION and further rolled back, which is just fine. Point to be noted here is if the ROLLBACK had succeeded in the INNER transaction, as soon as the CONTROL had returned from the INNER TRANSACTION to OUTER transaction, an error as below would have generated:-

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0."

FURTHER this ERROR would have been trapped by the CATCH of OUTER transaction, which would then attemp to ROLLBACT the transaction (that NO MORE EXISTS -- @@trancount is zero), that would generate an error as below:-

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

You can try this by simply removing the transaction name with ROLLBACK in TimsTestProc.

SO, Please let me know if am i missing anything or if I have a reason not admire error handling in SQL Server. :)

Thanks,
Chayan.

Kristen
Test

22859 Posts

Posted - 2010-04-08 : 09:37:56

"In SQL Server:-
- A ROLLBACK at any level of a nested transaction will rollback till the outermost transaction.
- You cannot use a ROLLBACK with the TRANSACTION NAME in an INNER TRANSACTION."

This is incorrect. You can used a Transaction Name to rollback to. However, it tends to cause outer transactions to abort becuase the Transaction Count is different to expected, you can work around this as per this post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141896#556968

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources