October 30, 2018 at 12:23 am
Comments posted to this topic are about the item ROLLBACK to a SAVEPOINT
October 30, 2018 at 12:59 am
Interesting question, thanks Sergey
back-to-basics question on a subject that i had forgotten all about (never use save points if i can help it)
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
October 30, 2018 at 7:18 am
Interesting question. I have never had a need for save points. It seems like if save points are needed perhaps a little more time in the design phase is appropriate. The part of this that is scary is that it seems to perpetuate the myth that nested transactions are real.
_______________________________________________________________
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/
October 30, 2018 at 9:23 am
Thanks, this question taught me something today!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 31, 2018 at 2:47 am
Sean Lange - Tuesday, October 30, 2018 7:18 AMThe part of this that is scary is that it seems to perpetuate the myth that nested transactions are real.
Great!
In this script the main bug is to open a nested transaction and rollback to the saved point. I think you can choose or no nested transaction and rollback to the saved point or start a named nested transaction and rollback to it instead of the saved point.
October 31, 2018 at 5:05 am
I’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?
October 31, 2018 at 7:00 am
nice question
ta
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
October 31, 2018 at 7:12 am
Ken Hiatt - Wednesday, October 31, 2018 5:05 AMI’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?
There're two begin transaction, so that's right. The rollback to saved_point is not the same as rollback of a transaction, you can see it as "restart from the saved_point".
November 6, 2018 at 11:58 am
Carlo Romagnano - Wednesday, October 31, 2018 7:12 AMKen Hiatt - Wednesday, October 31, 2018 5:05 AMI’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?There're two begin transaction, so that's right. The rollback to saved_point is not the same as rollback of a transaction, you can see it as "restart from the saved_point".
Agreed. And to answer the second question: yes, the end of the script (including both BEGIN TRAN statements) is implied.
Also, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:
How to rollback when 3 stored procedures are started from one stored procedure
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
November 6, 2018 at 4:55 pm
Solomon Rutzky - Tuesday, November 6, 2018 11:58 AMAlso, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:How to rollback when 3 stored procedures are started from one stored procedure
Take care, Solomon..
Wow....That's an awesome post on transactions!! Thanks for writing that and posting the link -
Sue
November 7, 2018 at 9:45 am
Sue_H - Tuesday, November 6, 2018 4:55 PMSolomon Rutzky - Tuesday, November 6, 2018 11:58 AMAlso, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:How to rollback when 3 stored procedures are started from one stored procedure
Take care, Solomon..
Wow....That's an awesome post on transactions!! Thanks for writing that and posting the link -
Sue
Thanks, and you are quite welcome :). And that reminds me:
I was going to mention to Sergey that the question, in its current form, is actually a little ambiguous since "0" is a possible answer, under a certain condition. If the session level setting of XACT_ABORT is ON, then @@TRANCOUNT will immediately go to 0 (including a rollback of the transaction itself). This question assumes that XACT_ABORT is OFF (which is typically the case), but it would be best to explicitly state that in the question, just to remove any possible confusion. You can see the effect below. It is the same code as in the question, but I set XACT_ABORT ON at the top, which then causes an error in the CATCH block since the only thing you can do in a CATCH block when XACT_ABORT is ON and an error happens is execute ROLLBACK. I also added the "GO" so that the error doesn't prevent the SELECT @@TRANCOUNT from showing that it is indeed 0. And of course the conditional ROLLBACK at the bottom to make testing easier when you change XACT_ABORT to OFF which then leaves the @@TRANCOUNT at 2.
SET XACT_ABORT ON;
BEGIN TRANSACTION
IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
DROP TABLE #tmpTable
CREATE TABLE #tmpTable(ID INT NOT NULL)
SAVE TRANSACTION T1
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO #tmpTable(ID)
VALUES (NULL)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
END CATCH
GO -- added to allow SELECT to work when XACT_ABORT is ON and causes error in CATCH block
SELECT @@TRANCOUNT AS 'TRANCOUNT'
IF (@@TRANCOUNT > 0)
BEGIN
PRINT 'Rolling back transaction...';
ROLLBACK;
END;
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply