Groups > comp.databases.ms-sqlserver > #342

SSE 2008: Transactions and Rollbacks: When are they done?

From	Gene Wirchenko <genew@ocis.net>
Newsgroups	comp.databases.ms-sqlserver, microsoft.public.sqlserver.programming
Subject	SSE 2008: Transactions and Rollbacks: When are they done?
Date	2011-05-17 15:09 -0700
Organization	A noiseless patient Spider
Message-ID	<91q5t6h56vk3db1ipt8eacd5407jc08c7f@4ax.com> (permalink)

Cross-posted to 2 groups.

Show all headers | View raw

Dear SQLers:

     I am working with error handling.  I have Erlang's code for error
reporting.  He suggests
          if @@tranlevel>0
             rollback
I have removed this from my code, and yet the rollbacks still occur. I
also have transaction levels being automatically created.  I would
like to understand what is going on.  Documentation that I have found
does not deal with this.

     At the outer level of my code, I have an insert statement wrapped
in a try block.  Note that I do not define a transaction here.  At the
first statement of the try block, @@trancount=0.  The insert is
executed.

     The insert trigger fires.  At the first statement of the insert
trigger's try block, @@trancount=1.  Why 1?

     A begin transaction is executed.  Now, @@trancount=2.

     Irrelevant stuff happens.

     A call to NiceString() with bad data is done.  NiceString()
returns an error code which the trigger code checks for.  A
raiserror(50001,16,1) is done.  (I have defined error 50001.)

     Now, in the catch block of the trigger, @@trancount=2.

     A lightly-modified (formatting mainly) version of Erlang's error
handler is executed.  @@trancount is unaffected.  Then, a raiserror()
is done to propagate the error to the caller.

     Back at the caller, @@trancount=0, but no rollback statement was
executed!  The error message is then printed.

     Supposedly, set xact_error on causes auto-rollbacks.  Fine, but
the above happens with set xact_error off (unless I am actually not
setting it in the right place.)

     According to
http://msdn.microsoft.com/en-us/library/ms188792.aspx, "Depending upon
the severity of the error, the entire transaction may be rolled back
even when SET XACT_ABORT is OFF."  What circumstances?  If the trigger
raiserror()'s severity is not high enough, the catch will not fire
anyway.  Is this what is meant here?

Sincerely,

Gene Wirchenko

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-17 15:09 -0700
  Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 07:27 +0000
    Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-18 10:50 -0700
      Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 23:39 +0200
        Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 13:10 -0700
          Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-19 23:38 +0200
            Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 23:20 -0700
              Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-20 07:32 +0000

csiph-web