October 9, 2014 at 7:20 am
patrik.ljunggren (10/9/2014)
There are no one completely correct answer.A1 are the most correct.
If the answer hade been "The insert succeeds and the update fails BUT when are both rolled back".
Have I right?
Without a try-catch error handling (or whatever) things just roll forward, the insert succeeds and the update doesn't.
October 9, 2014 at 7:21 am
John Mitchell-245523 (10/9/2014)
The question assumes that XACT_ABORT is set to OFF. I know that's the default, but it would be worth mentioning in the question or explanation for clarity and completeness.John
Good point. I totally forgot about that.
October 9, 2014 at 7:26 am
Here's an example from MSDN.
BEGIN TRANSACTION;
BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
Source: MSDN TRY CATCH
Enjoy!
October 9, 2014 at 8:01 am
I prefer to put the COMMIT inside the try block, rather than outside where an IF is necessary
So....
BEGIN TRY
BEGIN TRANSACTION;
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;
IF XACT_STATE != 0
ROLLBACK TRANSACTION;
END CATCH;
GO
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
October 9, 2014 at 8:14 am
Excellent question, Steve. I was sure that it was either the first or the fourth option; I just wasn't sure if the transaction would be rolled back automatically or not. As a result, I was shocked by the correct answer, as it seemed like something that a transaction "should protect against", namely, inconsistent data. I thought of it like working with two accounts: credit one account, then debit another; if the debit fails, then rollback the credit "automagically". Guess again. *Sigh*
So, I learned something today, which is the main reason for the QotD. Thanks again, Steve. 🙂
October 9, 2014 at 8:34 am
stephen.long.1 (10/9/2014)
I thought of it like working with two accounts: credit one account, then debit another; if the debit fails, then rollback the credit "automagically".
If you were using this query in a production environment, you of course would either have SET XACT_ABORT ON, or you would use TRY-CATCH logic, or both.
October 9, 2014 at 8:36 am
patrik.ljunggren (10/9/2014)
There are no one completely correct answer.A1 are the most correct.
If the answer hade been "The insert succeeds and the update fails BUT when are both rolled back".
Have I right?
No, if your "when" meant "then" that happens only when XACT_ABORT is set ON, and the default is OFF. When it's OFF, there is no rollback, and the insert is committed. Errors a bit more serious than this one can cause rollback even when XACT_ABORT is off, but apparently attempts to violate constraints are non-serious.
All this, by the way, strikes me as a good reason for setting XACT_ABORT on if you want genuine transactions instead of something with the inegrity of transactions "optimized" out.
Tom
October 9, 2014 at 9:35 am
Nice question about a important topic. Thanks Steve.
October 9, 2014 at 10:20 am
John Mitchell-245523 (10/9/2014)
The question assumes that XACT_ABORT is set to OFF.
When I first started working with SQL Server this behavior drove me nuts, so I definitely remember it. It seems to me this breaks the atomicity of the transaction but it is the default behavior Microsoft defined.
October 9, 2014 at 10:28 am
I think this comes up more often than any other issue in code reviews. "Just wrapping it in a transaction doesn't actually do anything. You need to check your error state and explicitly rollback or commit. Or rely on Xact Abort, but I will get grumpy if you do that."
October 9, 2014 at 11:54 am
cdesmarais 49673 (10/9/2014)
.... You need to check your error state and explicitly rollback or commit. ..."
+1
I do that. Sometimes I have write a script where it also contains the data of master and child tables. I track every step for success and then move to the insertion of next one... if one row fails, roll back entire process. I store all the error number and status in a temp table and at the end of the batch success or failure I will know to the exact moment to which row it failed and it helps me to fix the issue quicker.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
October 9, 2014 at 12:47 pm
GilaMonster (10/9/2014)
I prefer to put the COMMIT inside the try block, rather than outside where an IF is necessarySo....
BEGIN TRYBEGIN TRANSACTION;
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;
IF XACT_STATE != 0
ROLLBACK TRANSACTION;
END CATCH;
GO
Your answer makes more sense to me than example C in the link I provided earlier.
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the DELETE statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
-- Test XACT_STATE:
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means that there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
According to their example, testing XACT_STATE for != 0 is insufficient. But I'm not sure why the code in the catch block would be executing if the transaction can be committed. In that case, they are committing (or attempting to commit) the transaction twice. Once in the TRY and once again in the CATCH.
October 10, 2014 at 7:00 am
GilaMonster (10/9/2014)
I prefer to put the COMMIT inside the try block, rather than outside where an IF is necessarySo....
Ditto
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 10, 2014 at 7:29 am
Dave62 (10/9/2014)
According to their example, testing XACT_STATE for != 0 is insufficient.
Only if you plan to COMMIT in the catch block after an error has occurred (which there are probably valid reasons for). If you intend to roll it back on error no matter whether the transaction is commitable or not (the usual case), then just check that XACT_STATE is not 0 (meaning no open transactions)
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
October 13, 2014 at 10:50 am
I thought the Insert would fail because there was no char being inserted ('' being an empty string).
I missed the point of the question though, so I've learnt a valuable lesson.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply