July 16, 2010 at 11:47 am
I am calling a stored procedure from a VM running Windows Server 2003 via C# ADO.NET (framework 2.5). SQLServer 2008 (Dev) and the application are on the same VM. The connection string has MARS enabled and snapshot isolation is set on.
My stored procedure takes the form :
BEGIN TRANSACTION
DELETE FROM dbo.Table1 WHERE ID = @ID
INSERT INTO dbo.Table1 .....
INSERT INTO dbo.Table2 ( ...) SELECT ... FROM ....
COMMIT TRANSACTION
Table 1 populates fine.
Table 2 does not, even when I strip out all joins and conditions to leave a simple insert.
Any help on how to go about debugging this will be much appreciated. (I tried forcing the second insert to fail by introducing a data type mismatch and that did fail the whole transaction. I also tried doing the insert into Table2 first and it still failed to populate but Table1 worked. If I run the insert statement from management console it works.)
July 16, 2010 at 11:53 am
There's no error handling in that code. Hence any one of those statements could fail and, providing the error isn't severe enough to terminate the connection or terminate the batch, the others will run and the commit will run.
If you need a rollback should any of those fail, you need error handling.
Check out the Books Online sections on TRY ... CATCH blocks.
Unless there's error handling in the real proc that you left out for simplicity.
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 19, 2010 at 4:23 am
Thanks for the fast response.
The cause turned out to be a primary key violation on the table that wasn't populating. The insert was was using a SELECT statement that was unexpectedly returning multiple rows.
I would have expected an exception to be returned to my application but that did not happen. I've never known a transaction to partially commit like that - worrying. XACT_ABORT was off; I need to read up further on this stuff!
July 19, 2010 at 4:38 am
It's not that the transaction 'partially commits'. All a transaction assures you (other than locking) is that if the commit transaction never runs, the entire thing will roll back.
What can easily happen, since many SQL errors are not batch-terminating is that a statement fails, the error only terminates the statement so without error handling SQL simply continues execution of the next statement, finally reaching the commit.
A transaction does not guarantee that any error will result in a rollback, only errors severe enough to terminate the batch or connection. Any time the failure of one statement must result in a rollback of the entire transaction, you should have sufficient error handling to ensure that any error results in the ROLLBACK TRANSACTION running, not the COMMIT.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply