September 20, 2011 at 9:33 am
Hi, it seems like SQL Server silently rolls back my transaction in some cases.
If you try to drop a non-existing table, you get an error but the transaction is still active.
But if you try to drop a non-existing column, any open transactions are rolled back. :blink:
Is this normal behavior?
Case 1: Dropping non-existing column, transaction is silently rolled back. Why?
SET XACT_ABORT OFF
GO
BEGIN TRANSACTION
GO
PRINT @@TRANCOUNT
GO
ALTER TABLE ExistingTable DROP COLUMN NonExistingColumn
GO
PRINT @@TRANCOUNT
GO
ROLLBACK TRANSACTION
GO
1
Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'NonExistingColumn' does not exist in table 'ExistingTable'.
0
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Case 2: Dropping non-existing table, transaction is still open. This makes sense!
SET XACT_ABORT OFF
GO
BEGIN TRANSACTION
GO
PRINT @@TRANCOUNT
GO
ALTER TABLE NonExistingTable DROP COLUMN NonExistingColumn
GO
PRINT @@TRANCOUNT
GO
ROLLBACK TRANSACTION
GO
1
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "NonExistingTable" because it does not exist or you do not have permissions.
1
Please help me explain this, thanks!
September 20, 2011 at 12:52 pm
It seems that"SET XACT_ABORT OFF" define transaction behavior.
In accordance with BOL: "When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF."
Keywords are "in some cases" and "may be rolled back".
It seems that error severity is different when object does not exist or deleting nonexisting column on existing object.
September 20, 2011 at 1:16 pm
Welcome to the wackiness that is SQL error handling. This is pretty much the sole case where a statement fails, the connection remains open and the transaction rolls 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
September 22, 2011 at 11:40 am
Thank you, so it seems like you can never be sure how SQL server will treat errors inside a transaction.
If you look at the code in the following example, its not very obvious whats gonna happen:
SET XACT_ABORT OFF
GO
BEGIN TRANSACTION
GO
SELECT CAST('A' AS int)
GO
DROP TABLE MyPreciousTable
GO
ROLLBACK TRANSACTION
GO
The result is that the table is dropped, because conversion errors also roll back transactions.
Wackiness indeed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply