August 28, 2012 at 4:40 am
Hello,
I wonder if someone could just confirm for me what should (or should not) happen when a statement within a BEGIN TRANSACTION .... COMMIT fails.
I have two INSERT statements within the transaction that insert into two tables. The second INSERT failed due to an overflow of a TINYINT table column. I had assumed that when something like this happened that the entire transaction would auto rollback as a result of a failure to complete. As someone quite new to all this, it sort of seems logical that such failures should rollback the transaction without manual intervention, but it does not appear to do that - i.e I have entries written into the first table correctly from the first INSERT executed within the transaction block.
Am I going to have to use BEGIN TRY ... CATCH blocks and ROLLBACK whenever I want a transaction to cleanly rollback when an error occurs?
Regards
Steve
August 28, 2012 at 4:46 am
raotor (8/28/2012)
Hello,Am I going to have to use BEGIN TRY ... CATCH blocks and ROLLBACK whenever I want a transaction to cleanly rollback when an error occurs?
This is the normal way of handling rollbacks.
It gives you more control over what happens & you can output your own error message.
August 28, 2012 at 4:55 am
Thinking about it, you can set up automatic transactions, although I've never done it:
August 28, 2012 at 5:03 am
CREATE TABLE TEST(
id INT NOT NULL,
name VARCHAR(50)
)
--This would still insert because two inserts are considered different transactions because you
--explicitly didn't committed it yet!
BEGIN TRAN
INSERT INTO TEST(id, name) VALUES(1,'a')
INSERT INTO TEST(name) VALUES('b')
COMMIT TRAN
--Any code that fails in the begin try scope, it will shift and automatically go to the begin catch scope
--which would eventually rollback the whole transaction from the statement that fails
--to the statement after the begin try
BEGIN TRAN
BEGIN TRY
INSERT INTO TEST(id, name) VALUES(2,'c')
INSERT INTO TEST(name) VALUES('d')
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
SELECT * FROM TEST
DROP TABLE TEST
You can refer to the link for controlling transactions.
http://msdn.microsoft.com/en-us/library/ms175523%28v=sql.105%29
August 28, 2012 at 5:07 am
http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
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
August 28, 2012 at 5:09 am
laurie-789651 (8/28/2012)
Thinking about it, you can set up automatic transactions, although I've never done it:
Thanks very much for the link.
I will take a look at it. Does seem to me though that having an alternative to manually catching errors for the purpose of reversing a transaction would make sense (I hope!).
August 28, 2012 at 5:17 am
There is one. See the blog post I referenced.
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
August 28, 2012 at 6:08 am
GilaMonster (8/28/2012)
There is one. See the blog post I referenced.
Just read it and it's most helpful and just what I was looking for. Thanks.
August 28, 2012 at 7:07 am
GilaMonster (8/28/2012)
There is one. See the blog post I referenced.
Hello Gail,
Just one thing I've noticed that I think is important to clarify. When using the SET XACT_ABOIRT ON it should be noted that nothing after the COMMIT is executed until a subsequent GO is reached with more statements thereafter.
Given that my transaction block occurs within a stored procedure and that there is code following that block, using XACT_ABORT ON will effectively abort the entire SP should an error occur within the transaction block.
OK, not so much of a problem as using BEGIN TRY .. CATCH to manually deal with errors is the alternative, but this point almost tripped me up so I think it's worth mentioning.
Regards
Steve
August 28, 2012 at 7:32 am
Hadn't noticed that because I use TRY .. CATCH all the time. Honestly, you should be catching errors and handling them, not just silently rolling stuff back.
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply