471,570 Members | 966 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,570 software developers and data experts.

MSDTC and Delete transactions

Hello
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@@ERROR value. There are just DELETE statement and a plain RETURN.

thanks
Sunit

Aug 28 '06 #1
1 2116
sjoshi (sj****@ingr.com) writes:
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@@ERROR value. There are just DELETE statement and a plain RETURN.
In a normal stored procedure if you have:

BEGIN TRANSACTION
DELETE tbl WHERE ....
DELETE tbl2 WHERE ....
COMMIT TRANSACTION

you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.

A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.

But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @@error
or make sure XACT_ABORT is ON.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by JIMMIE WHITAKER | last post: by
reply views Thread by | last post: by
reply views Thread by Srini Eadha | last post: by
6 posts views Thread by cylt | last post: by
1 post views Thread by Jimbo | last post: by
6 posts views Thread by =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.