July 17, 2013 at 5:20 pm
When testing Stored Procedures, can you run something like
BEGIN TRAN
EXEC sp_deleteSomeRecords "Varchar Parameter", 999, NULL
-- COMMIT TRAN
-- ROLLBACK TRAN
... to protect the data in case sometime goes wrong when the procedure itself contains BEGIN/COMMIT/ROLLBACK TRAN statements and a TRY/CATCH block?
July 18, 2013 at 12:09 am
Yes, it's possible!
But when the transaction inside the stored procedure is rolled back, it will state an error message: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."
Also the outer ROLLBACK will fail because there is no open transaction, error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.".
You have to build error handling in your code to cover these errors.
July 18, 2013 at 4:57 am
It will be better if you used Transaction in your stored procedure itself.......
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2013 at 10:32 am
Since the procedure uses multiple update statements depending on the parameters entered, I have BEGIN TRAN inside the procudure with a COMMIT TRAN at the end of the TRY block and a ROLLBACK TRAN inside a CATCH block. This will undo incomplete changes to the data in case of a SQL error, but not helpful if the syntax is correct but a logic error causes the data to be updated incorrectly. This is why for testing I want to run the SP inside a BEGIN TRAN block - in case an unexpected number of rows are affected during testing.
If there is an error (like a missing WHERE clause) that affects all rows in a table and COMMIT TRAN is run inside the SP, can that be undone by a ROLLBACK TRAN outside the SP provided there was a BEGIN TRAN before the SP was launched, in addition to the BEGIN TRAN inside the SP?
July 18, 2013 at 12:04 pm
The answer is yes. If the code in the inner BEGIN/COMMIT TRAN runs without a SQL error, I was able to undo the changes with the outer ROLLBACK TRAN. And when I delerately created a SQL error (by trying to set a DATE type column to 'Invalid') I got the error described above by HanShi.
July 18, 2013 at 12:45 pm
dan-572483 (7/18/2013)
The answer is yes. If the code in the inner BEGIN/COMMIT TRAN runs without a SQL error, I was able to undo the changes with the outer ROLLBACK TRAN. And when I delerately created a SQL error (by trying to set a DATE type column to 'Invalid') I got the error described above by HanShi.
This is because nested transactions in sql server are not available. You can begin as many as you like but after the first all it does is increment @@TRANCOUNT. Then when there is a commit or rollback they are ALL affected and @@TRANCOUNT is returned to 0.
Nested transactions seem logical, MS even mentions them somewhere in the documentation but the reality is, they are a farce.
_______________________________________________________________
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 18, 2013 at 12:48 pm
Sean Lange (7/18/2013)
You can begin as many as you like but after the first all it does is increment @@TRANCOUNT. Then when there is a commit or rollback they are ALL affected and @@TRANCOUNT is returned to 0.
When there is a rollback, all are affected, all uncommitted changes are undone and @@trancount is set to 0
When there is a commit, @@trancount is decremented by 1. If that would set it to 0, then the transaction is committed, otherwise nothing else occurs.
Hence you need to commit as many times as you begin tran, but you only need to roll back once.
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 18, 2013 at 12:54 pm
GilaMonster (7/18/2013)
Sean Lange (7/18/2013)
You can begin as many as you like but after the first all it does is increment @@TRANCOUNT. Then when there is a commit or rollback they are ALL affected and @@TRANCOUNT is returned to 0.When there is a rollback, all are affected, all uncommitted changes are undone and @@trancount is set to 0
When there is a commit, @@trancount is decremented by 1. If that would set it to 0, then the transaction is committed, otherwise nothing else occurs.
Hence you need to commit as many times as you begin tran, but you only need to roll back once.
Thanks for the clarification Gail. I seem to remember at one point you linking an article that explained the nested transactions myth pretty well. Any chance you know what that was?
_______________________________________________________________
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 18, 2013 at 12:56 pm
Off the top of my head, no. Don't think I ever got around to writing one. Might have been part of Paul Randal's myth a day series.
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 18, 2013 at 12:58 pm
I just found this one in that series. 😛
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
_______________________________________________________________
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 18, 2013 at 2:31 pm
Interesting! When tinkering with data, I've often forgotten to to run COMMIT TRAN until the last change, then just ran COMMIT TRAN repeatedly until "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION" appeared. I just assumed that my data edits were being committed in the reverse order of how they were run.
I created the script below to play around with these scenarios. What I learned was
1) These transations are not really committed until the number of COMMIT TRANs run is equal to the number of BEGIN TRANS run.
2) A ROLLBACK TRAN at any point will cancel ALL edits since the last committed transaction.
Good to know for manual data tweaking, because if you fall behind in your COMMITs, a single ROLLBACK could cancel an uncertain amount of work.
As for my original question, the code I posted is a good developent testing practice because it allows you to undo unexpected changes that may have been "committed" inside the procedure.
CREATE TABLE dbo.NestedTranTest
(Col1 Int NULL,
Col2 Int NULL,
Col3 Int NULL,
Col4 Int NULL)
GO
Begin Tran
INSERT NestedTranTest
Values(1,null,null,null)
GO
Begin Tran
update NestedTranTest
set Col2 = 2
GO
Begin Tran
update NestedTranTest
set Col3 = 3
GO
Begin Tran
update NestedTranTest
set Col4 = 4
GO
commit tran
GO
commit tran
GO
rollback tran
GO
Commit Tran
select * from NestedTranTest
/*
delete NestedTranTest
*/
July 19, 2013 at 12:03 am
dan-572483 (7/18/2013)
Interesting! When tinkering with data, I've often forgotten to to run COMMIT TRAN until the last change, then just ran COMMIT TRAN repeatedly until "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION" appeared. I just assumed that my data edits were being committed in the reverse order of how they were run.I created the script below to play around with these scenarios. What I learned was
1) These transations are not really committed until the number of COMMIT TRANs run is equal to the number of BEGIN TRANS run.
2) A ROLLBACK TRAN at any point will cancel ALL edits since the last committed transaction.
Good to know for manual data tweaking, because if you fall behind in your COMMITs, a single ROLLBACK could cancel an uncertain amount of work.
As for my original question, the code I posted is a good developent testing practice because it allows you to undo unexpected changes that may have been "committed" inside the procedure.
CREATE TABLE dbo.NestedTranTest
(Col1 Int NULL,
Col2 Int NULL,
Col3 Int NULL,
Col4 Int NULL)
GO
Begin Tran
INSERT NestedTranTest
Values(1,null,null,null)
GO
Begin Tran
update NestedTranTest
set Col2 = 2
GO
Begin Tran
update NestedTranTest
set Col3 = 3
GO
Begin Tran
update NestedTranTest
set Col4 = 4
GO
commit tran
GO
commit tran
GO
rollback tran
GO
Commit Tran
select * from NestedTranTest
/*
delete NestedTranTest
*/
Thanks Dan its a nice example to play around transaction for initial purpose..... 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply