July 20, 2012 at 2:12 pm
A colleague of mine who shall remain nameless (I won't tell, Garth 😉 ) has a proc that calls a proc that calls a proc.. The first two procs are using Try/Catch, as well as Begin Trans/commit blocks. His problem is that the Outer-most proc has an Update statement that, when it fails, gives more than just an error for the failed UPdate statement... It's saying there is a mismatch in the Begin Transactions / Commit counts... I think the update statement, which runs before the final commit, is causing an error of a severity level high enough to crash the whole thing leaving an open Transaction, thus the additional error.... Here's the general structure.
SP_PROC_1
Begin
Begin Transaction
"Do something here"
Exec Proc_2
UPdate X
Set X.y = 'Yadda yadda'
Commit;
End
Proc_2
Begin
Begin Trans
Print 'BLAH'
Exec Proc_3 --doesn't really do anything.
Commit;
End
Proc_3 just runs a select statement.
When he runs Exec SP_Proc_1 if the Update statement in the first transaction fails, then he gets the expected message about the failed update query; however, he also gets a message about the failed update statement; however, then he gets an additional error message having to do with a mismatch in the number of Begin Trans vs Commit statements. I think it is becuase the failed update statement klls the whole proc and then throws an additional error because of the hanging Commit.
Also, might be something about the server possibly not being set to automaticly roll back open transactions.
Could someone let me know if I'm on the right track with this? I bet him a coke that he was wrong.
Thanks
Crusty
July 20, 2012 at 2:44 pm
You can't really nest transaction like that.
Here is a very simple example of what happens.
Here I will create a proc called CrashSproc. This proc will do what it's name implies with an inner try/catch and a transaction like you describe is happening.
Then I will create another proc Proc1 that has a try catch with a transaction and calls the CrashSproc.
create procedure CrashSproc
as begin
begin try
begin transaction
select @@TRANCOUNT, 'TranCount inside CrashSproc'
select 1/0 --divide by 0 error
commit transaction
end try
begin catch
rollback transaction
select @@TRANCOUNT, 'TranCount after rollback inside CrashSproc'
end catch
end
go
create procedure Proc1
as begin
begin try
begin transaction
select @@TRANCOUNT, 'TranCount before CrashSproc'
exec CrashSproc
select @@TRANCOUNT, 'TranCount after CrashSproc'
commit transaction
end try
begin catch
rollback transaction
end catch
end
go
exec Proc1
What you will notice is that TranCount before CrashSproc = 1
Then we start a transaction in CrashSproc and the count = 2 as you would expect. However this code now throws and exception and we ROLLBACK TRANSACTION. You will then notice that @@TRANCOUNT = 0
To get around this you need to either drop the outer transaction or the inner transaction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 3:15 pm
The proc runs fine when he feeds the Update statement a good value. It was in testing that he discovered the interested Error messages. Either way, I though he should drop the inner Transaction. As none of the statements within the entire thing are very complex or taxing, he could probably handle the thing as one transaction.
Was I even close regarding the errors?
July 20, 2012 at 3:29 pm
CptCrusty1 (7/20/2012)
The proc runs fine when he feeds the Update statement a good value. It was in testing that he discovered the interested Error messages. Either way, I though he should drop the inner Transaction. As none of the statements within the entire thing are very complex or taxing, he could probably handle the thing as one transaction.Was I even close regarding the errors?
The fact that it works when everything is fine makes this even more confusing. If you change that CrashSproc to select 1 instead of the divide by zero error it will run fine and the tran counts will be exactly what you expect all the way through.
The rollback will rollback all the transactions. Thus the reason the number is out of synch.
If the transactions can be removed from the "inner" sprocs that would work just fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2012 at 3:54 pm
His "CrashProc" runs fine every time. Its when the control goes back to the Outer Proc and runs the next statement, which is an update statement, that we get the problem. He's trying to make the inner proc success/failure invisible to the outer proc.
Begin Trans
Begin Trans
Exec SP_Yadda
Commit;
Update X
Set Col1="Yowza"
Commit;
I'm having trouble understanding his over all goal, so I'm focusing instead on what I see as an issue... If the Inner Transaction has a Try/Catch/Rollback, but runs fine, commits, exits to the outer proc, and then the Outer proc fails, the Inner Proc, since it successfully (I think) processes it's own commit, then the outer proc failing should have no affect on the results of the inner proc, right?
Thanks again.
Crusty.
July 20, 2012 at 3:58 pm
If the Inner Transaction has a Try/Catch/Rollback, but runs fine, commits, exits to the outer proc, and then the Outer proc fails, the Inner Proc, since it successfully (I think) processes it's own commit, then the outer proc failing should have no affect on the results of the inner proc, right?
I've discovered that this isn't correct. The inner transaction won't be committed to disk. The outer transaction has to complete and IT's commit finishes the whole thing.
July 20, 2012 at 4:27 pm
Nested transactions are a lie. They don't really exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2012 at 10:45 pm
GilaMonster (7/20/2012)
Nested transactions are a lie. They don't really exist.
Sure they do. Just like Bigfoot and the Loch Ness Monster. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply