March 15, 2010 at 5:35 am
Hi,
Looking through BOL and even the SQL created by, for example, adding a row to a table in SQL Management Studio, I see lots of:
BEGIN TRANSACTION
<some SQL>
COMMIT
with no corresponding ROLLBACK.
Now, I presume that the authors expect an error could occur in the SQL , which is why they wrapped it in a transaction so it could all work/fail together.
Why then not include a ROLLBACK in the case of an error?
Surely this would mean that transations could get left open, with all the problems that causes?
On the other hand, if this type of code is used so much, is it really bad? Am I missing something?
For example, if I use a transaction, like the example above, in a stored procedure and an error occurs, does it automatically get rolled back when the SP finishes, if it hasn't already been commited?
Thanks!
March 15, 2010 at 6:11 am
Hi Cyberspy,
read this article: http://msdn.microsoft.com/en-us/library/ms175523%28v=SQL.100%29.aspx (Errors During Transaction Processing)
Hope this helps
March 15, 2010 at 6:29 am
Cyberspy (3/15/2010)
Now, I presume that the authors expect an error could occur in the SQL , which is why they wrapped it in a transaction so it could all work/fail together. Why then not include a ROLLBACK in the case of an error?
That link is worth reading. Here is the clickable version. The section highlighted by roman.konz is most relevent to your question.
To the wider points though. It is definitely best practice to use TRY...CATCH, test XACT_STATE() and explicitly ROLLBACK or COMMIT transactions, but it does make sense to be sensible about it. The idea is to not leave transactions open, so if all the logical code paths result in a successful COMMIT (even after a statement-terminating error) or a batch-abort plus automatic ROLLBACK, it is fine.
Trigger code does check transaction count on entry and exit, and will raise an error and rollback any open transaction if it detects a difference.
Stored procedures do *not* do this - and we would not want them to - so be aware of that.
I found it helpful to try some tests with transactions, procedures, and various settings of XACT_ABORT, as a learning exercise. A good one is to try to break the sample you provided. Code something inside the transaction to throw a real error, and see if you can 'break it' - i.e. leave a transaction hanging.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply