Transaction commit & rollback in SP's

• Cyberspy

  SSC Eights!

  Points: 920

  More actions

  March 15, 2010 at 5:35 am

  #216668

  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!

• roman.konz

  SSC Enthusiast

  Points: 133

  More actions

  March 15, 2010 at 6:11 am

  #1133620

  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

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 15, 2010 at 6:29 am

  #1133640

  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

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi