SET XACT_ABORT just doesn't work...

SET XACT_ABORT just doesn't work...

Post by Greg Linwoo » Sat, 26 Jan 2002 20:55:41



SET XACT_ABORT isn't working to specification.

The following code works fine on 6.5, but orphans transactions in 7.0 and
STILL in 2000

set xact_abort on
begin transaction
 select * from a_table_that_doesnt_exist
 --exec('select * from a_table_that_doesnt_exist')

commit
return
do_rollback:
rollback
return
go

 begin
  print 'rolling back orphaned transaction'
  rollback
 end
go

If you toggle the exec() version of the select, it works fine, but I'm not
happy about having to use Exec() everywhere for a variety of reasons - eg:
string construction to include parameters, re-use of execution plans etc,
etc...

This is a terrible bug - orphaned transactions might be holding exclusive
locks on database objects indefinitely - especially where db connections are
pooled under an app or web server.

The fact that SET XACT_ABORT works "most" of the time is not good enough
because this bug is deadly enough to bring an app down on its own.


transaction is orphaned before it gets a chance to run.

I've scoured BOL and there's no doubt about it - SET XACT_ABORT is supposed
to rollback on "any runtime" error. Perhaps this one has snuck in as a
result of deferred name resolution in 7.0?

I read the section on Transact-SQL error handling in Ken Henderson'd new
"Guru's" book (great book Ken) and he suggests the use of Exec() or nesting
calls to stored procs (either of which would be particularly * to

solution.

I'm wondering if anyone else has any other ideas?

I've loooked hard at this - but it really seems to me to be a bug, so what
we REALLY need here is a FIX NOW - another SP if necessary - not when Yukon
comes out and brings in structured exception handling because that may be a
long way off.

Cheers,
Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by David S. Patching [M » Sat, 26 Jan 2002 23:26:04


Greg,

Is the following excerpt from the SQL 2000 Books Online topic 'SET
XACT_ABORT' relevant to the behavior that you are seeing?

"When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time
error, the entire transaction is terminated and rolled back. When OFF, only
the Transact-SQL statement that raised the error is rolled back and the
transaction continues processing. Compile errors, such as syntax errors,
are not affected by SET XACT_ABORT."

Specifically, the line "Compile errors, such as syntax errors, are not
affected by SET XACT_ABORT"

Thank you,
David

David S. Patching
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
<http://www.microsoft.com/security>.

 
 
 

SET XACT_ABORT just doesn't work...

Post by Greg Linwoo » Sun, 27 Jan 2002 01:06:50


Thanks David..

I read that topic, but:

(a) There's no syntax error in my code.. just a (potential) deferred name
resolution error, which should be treated as a run time error, not a compile
time error.
(b) The code compiles, and runs. Note there is another example further down
the thread where an UPDATE is also included - it compiles, runs and then the
error occurs at the SELECT (against a non-existent table)
(c) Look at the BOL topic "Batches" - it says the following about run-time
and compile-time errors:
A compile error, such as a syntax error, prevents the compilation of the
execution plan, so none of the statements in the batch are executed.
   *** (note that my batch compiles and runs) ***
A run-time error, such as an arithmetic overflow or a constraint violation,
has one of two effects:
 - Most run-time errors stop the current statement and the statements that
follow it in the batch.
 - A few run-time errors, such as constraint violations, stop only the
current statement. All the remaining statements in the batch are executed.
   *** (this is the bit that scares me most... which run-time errors are
going to terminate my batch, exactly???)

The answer's obvious - any error that occurs at run time should be treated
as a run time error. A compile time error should not allow ANY processing in
the batch.

A deferred name resolution error should raise a run time error. Otherwise,
we're going to get orphaned transactions, and our apps are gonna crash.

btw - whether or not if you accept this topic as a suitable explanation of
the behaviour displayed in my example, you still haven't actually solved the
problem.

Cheers,
Greg Linwood
:c/



Quote:> Greg,

> Is the following excerpt from the SQL 2000 Books Online topic 'SET
> XACT_ABORT' relevant to the behavior that you are seeing?

> "When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time
> error, the entire transaction is terminated and rolled back. When OFF,
only
> the Transact-SQL statement that raised the error is rolled back and the
> transaction continues processing. Compile errors, such as syntax errors,
> are not affected by SET XACT_ABORT."

> Specifically, the line "Compile errors, such as syntax errors, are not
> affected by SET XACT_ABORT"

> Thank you,
> David

> David S. Patching
> Microsoft Support

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure?  For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> <http://www.microsoft.com/security>.

 
 
 

SET XACT_ABORT just doesn't work...

Post by Ivan Arjentinsk » Tue, 29 Jan 2002 06:37:46


Quote:> This is a terrible bug - orphaned transactions might be holding exclusive
> locks on database objects indefinitely - especially where db connections
are
> pooled under an app or web server.

This sounds too harsh for me. No serious database system uses dynamic sql,
run-time table creation and other administrative tools in real transactions.

This sounds more like bad database design.

Quote:> SET XACT_ABORT isn't working to specification.

Seems so, very strictly speaking. However, try to rework your schema. If you
have problems finding out how to avoid dynamic table creation, post in this
newsgroup.

--
Ivan Arjentinski


> SET XACT_ABORT isn't working to specification.

> The following code works fine on 6.5, but orphans transactions in 7.0 and
> STILL in 2000

> set xact_abort on
> begin transaction
>  select * from a_table_that_doesnt_exist
>  --exec('select * from a_table_that_doesnt_exist')

> commit
> return
> do_rollback:
> rollback
> return
> go

>  begin
>   print 'rolling back orphaned transaction'
>   rollback
>  end
> go

> If you toggle the exec() version of the select, it works fine, but I'm not
> happy about having to use Exec() everywhere for a variety of reasons - eg:
> string construction to include parameters, re-use of execution plans etc,
> etc...

> This is a terrible bug - orphaned transactions might be holding exclusive
> locks on database objects indefinitely - especially where db connections
are
> pooled under an app or web server.

> The fact that SET XACT_ABORT works "most" of the time is not good enough
> because this bug is deadly enough to bring an app down on its own.


> transaction is orphaned before it gets a chance to run.

> I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
supposed
> to rollback on "any runtime" error. Perhaps this one has snuck in as a
> result of deferred name resolution in 7.0?

> I read the section on Transact-SQL error handling in Ken Henderson'd new
> "Guru's" book (great book Ken) and he suggests the use of Exec() or
nesting
> calls to stored procs (either of which would be particularly * to
> implement for different reasons). Another idea he has is to check

> solution.

> I'm wondering if anyone else has any other ideas?

> I've loooked hard at this - but it really seems to me to be a bug, so what
> we REALLY need here is a FIX NOW - another SP if necessary - not when
Yukon
> comes out and brings in structured exception handling because that may be
a
> long way off.

> Cheers,
> Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Greg Linwoo » Tue, 29 Jan 2002 08:13:01


Thanks Ivan...

I'd hardly call the use of temporary tables bad design!!

I use them regularly for hierarchical queries, but many others advocate
their use for all sorts of operations!

Temp tables are a "feature" of SQL Server, so I "want" to use them without
risk of my transactions being orphaned!!

I won't retract - this is, in fact, a terrible bug. It was introduced in 7.0
and needs to be removed by service pack ASAP.

btw - I'm not trying to denounce SQL Server - anyone who knows me knows how
much I've loved using it for many years, but this one has really
disappointed me and I'm worried about it huring my clients..

I'm actually looking for genuine work-arounds here..

Cheers,
Greg Linwood


> > This is a terrible bug - orphaned transactions might be holding
exclusive
> > locks on database objects indefinitely - especially where db connections
> are
> > pooled under an app or web server.

> This sounds too harsh for me. No serious database system uses dynamic sql,
> run-time table creation and other administrative tools in real
transactions.

> This sounds more like bad database design.

> > SET XACT_ABORT isn't working to specification.
> Seems so, very strictly speaking. However, try to rework your schema. If
you
> have problems finding out how to avoid dynamic table creation, post in
this
> newsgroup.

> --
> Ivan Arjentinski



> > SET XACT_ABORT isn't working to specification.

> > The following code works fine on 6.5, but orphans transactions in 7.0
and
> > STILL in 2000

> > set xact_abort on
> > begin transaction
> >  select * from a_table_that_doesnt_exist
> >  --exec('select * from a_table_that_doesnt_exist')

> > commit
> > return
> > do_rollback:
> > rollback
> > return
> > go

> >  begin
> >   print 'rolling back orphaned transaction'
> >   rollback
> >  end
> > go

> > If you toggle the exec() version of the select, it works fine, but I'm
not
> > happy about having to use Exec() everywhere for a variety of reasons -
eg:
> > string construction to include parameters, re-use of execution plans
etc,
> > etc...

> > This is a terrible bug - orphaned transactions might be holding
exclusive
> > locks on database objects indefinitely - especially where db connections
> are
> > pooled under an app or web server.

> > The fact that SET XACT_ABORT works "most" of the time is not good enough
> > because this bug is deadly enough to bring an app down on its own.


> > transaction is orphaned before it gets a chance to run.

> > I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
> supposed
> > to rollback on "any runtime" error. Perhaps this one has snuck in as a
> > result of deferred name resolution in 7.0?

> > I read the section on Transact-SQL error handling in Ken Henderson'd new
> > "Guru's" book (great book Ken) and he suggests the use of Exec() or
> nesting
> > calls to stored procs (either of which would be particularly * to
> > implement for different reasons). Another idea he has is to check

> > solution.

> > I'm wondering if anyone else has any other ideas?

> > I've loooked hard at this - but it really seems to me to be a bug, so
what
> > we REALLY need here is a FIX NOW - another SP if necessary - not when
> Yukon
> > comes out and brings in structured exception handling because that may
be
> a
> > long way off.

> > Cheers,
> > Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Ivan Arjentinsk » Tue, 29 Jan 2002 18:30:58


Greg,

Your original post didn't use temp tables. It refered to normal tables. I
see now that in later post, in the attachment you use temp tables. Temp
tables, however are just like normal tables, except that they are in tempdb.

On the topic:
You could check for the existence of a temp table before using it. So there
is an easy work-around. It seems to me also as better programming practice,
compared to direct selection from the table, depending on XACT_ABORT. Also
temp tables should usually be created/dropped in one and the same SP.

Again, I'm not saying there is no bug. There might be. The behaviour is
little bit strange. Others would comment this.

If my previous post sounded too rude, please accept my apology.
--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....) and desired result set when asking
for assistance is highly appreciated
----------------------------------------------------------------------


> Thanks Ivan...

> I'd hardly call the use of temporary tables bad design!!

> I use them regularly for hierarchical queries, but many others advocate
> their use for all sorts of operations!

> Temp tables are a "feature" of SQL Server, so I "want" to use them without
> risk of my transactions being orphaned!!

> I won't retract - this is, in fact, a terrible bug. It was introduced in
7.0
> and needs to be removed by service pack ASAP.

> btw - I'm not trying to denounce SQL Server - anyone who knows me knows
how
> much I've loved using it for many years, but this one has really
> disappointed me and I'm worried about it huring my clients..

> I'm actually looking for genuine work-arounds here..

> Cheers,
> Greg Linwood



> > > This is a terrible bug - orphaned transactions might be holding
> exclusive
> > > locks on database objects indefinitely - especially where db
connections
> > are
> > > pooled under an app or web server.

> > This sounds too harsh for me. No serious database system uses dynamic
sql,
> > run-time table creation and other administrative tools in real
> transactions.

> > This sounds more like bad database design.

> > > SET XACT_ABORT isn't working to specification.
> > Seems so, very strictly speaking. However, try to rework your schema. If
> you
> > have problems finding out how to avoid dynamic table creation, post in
> this
> > newsgroup.

> > --
> > Ivan Arjentinski



> > > SET XACT_ABORT isn't working to specification.

> > > The following code works fine on 6.5, but orphans transactions in 7.0
> and
> > > STILL in 2000

> > > set xact_abort on
> > > begin transaction
> > >  select * from a_table_that_doesnt_exist
> > >  --exec('select * from a_table_that_doesnt_exist')

> > > commit
> > > return
> > > do_rollback:
> > > rollback
> > > return
> > > go

> > >  begin
> > >   print 'rolling back orphaned transaction'
> > >   rollback
> > >  end
> > > go

> > > If you toggle the exec() version of the select, it works fine, but I'm
> not
> > > happy about having to use Exec() everywhere for a variety of reasons -
> eg:
> > > string construction to include parameters, re-use of execution plans
> etc,
> > > etc...

> > > This is a terrible bug - orphaned transactions might be holding
> exclusive
> > > locks on database objects indefinitely - especially where db
connections
> > are
> > > pooled under an app or web server.

> > > The fact that SET XACT_ABORT works "most" of the time is not good
enough
> > > because this bug is deadly enough to bring an app down on its own.


> > > transaction is orphaned before it gets a chance to run.

> > > I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
> > supposed
> > > to rollback on "any runtime" error. Perhaps this one has snuck in as a
> > > result of deferred name resolution in 7.0?

> > > I read the section on Transact-SQL error handling in Ken Henderson'd
new
> > > "Guru's" book (great book Ken) and he suggests the use of Exec() or
> > nesting
> > > calls to stored procs (either of which would be particularly * to
> > > implement for different reasons). Another idea he has is to check

> > > solution.

> > > I'm wondering if anyone else has any other ideas?

> > > I've loooked hard at this - but it really seems to me to be a bug, so
> what
> > > we REALLY need here is a FIX NOW - another SP if necessary - not when
> > Yukon
> > > comes out and brings in structured exception handling because that may
> be
> > a
> > > long way off.

> > > Cheers,
> > > Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Greg Linwoo » Tue, 29 Jan 2002 21:33:40


Thanks for your posts Ivan - I really appreciate your comments...

I don't think it really matters whether or not the tables are temporary or
permanent - the issue is just that XACT_ABORT isn't working as documented.
BOL is very clear on XACT_ABORT - it is supposed to rollback any transaction
where a run-time error occurs and this is simply not happening in my
examples.

Perhaps you could code defensively to determine the existence of system
objects (permanent or temporary), but I just don't want to have to run the
(un)necessary SELECT commands against sysobjects just to check that a table
exists for every statement I have in my transactions. This would create a
huge concurrency problem in any application.

I just want SET XACT_ABORT to do what it's supposed to do - on ANY run-time
error during a transaction it is supposed to protect me from my transactions
being orphaned - no matter what the error. The very last thing I want is for
those transactions to live on and on until the connection maybe / eventually
gets dropped before the transaction's locked resources are released.

I have no doubt that this is a bug - & let me explain my rationale by
arguing the reverse.. If it is not a bug - there is NO WAY of rolling back a
transaction in this scenario, because the batch will be terminated every
time the error occurs, orphaning the transaction before any corrective code
you may write can run. )c:

I'm sure we need a service pack on this one.

Cheers,
Greg Linwood


> Greg,

> Your original post didn't use temp tables. It refered to normal tables. I
> see now that in later post, in the attachment you use temp tables. Temp
> tables, however are just like normal tables, except that they are in
tempdb.

> On the topic:
> You could check for the existence of a temp table before using it. So
there
> is an easy work-around. It seems to me also as better programming
practice,
> compared to direct selection from the table, depending on XACT_ABORT. Also
> temp tables should usually be created/dropped in one and the same SP.

> Again, I'm not saying there is no bug. There might be. The behaviour is
> little bit strange. Others would comment this.

> If my previous post sounded too rude, please accept my apology.
> --
> Ivan Arjentinski
> ----------------------------------------------------------------------
> Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
> scripts), sample data (INSERT INTO....) and desired result set when asking
> for assistance is highly appreciated
> ----------------------------------------------------------------------



> > Thanks Ivan...

> > I'd hardly call the use of temporary tables bad design!!

> > I use them regularly for hierarchical queries, but many others advocate
> > their use for all sorts of operations!

> > Temp tables are a "feature" of SQL Server, so I "want" to use them
without
> > risk of my transactions being orphaned!!

> > I won't retract - this is, in fact, a terrible bug. It was introduced in
> 7.0
> > and needs to be removed by service pack ASAP.

> > btw - I'm not trying to denounce SQL Server - anyone who knows me knows
> how
> > much I've loved using it for many years, but this one has really
> > disappointed me and I'm worried about it huring my clients..

> > I'm actually looking for genuine work-arounds here..

> > Cheers,
> > Greg Linwood



> > > > This is a terrible bug - orphaned transactions might be holding
> > exclusive
> > > > locks on database objects indefinitely - especially where db
> connections
> > > are
> > > > pooled under an app or web server.

> > > This sounds too harsh for me. No serious database system uses dynamic
> sql,
> > > run-time table creation and other administrative tools in real
> > transactions.

> > > This sounds more like bad database design.

> > > > SET XACT_ABORT isn't working to specification.
> > > Seems so, very strictly speaking. However, try to rework your schema.
If
> > you
> > > have problems finding out how to avoid dynamic table creation, post in
> > this
> > > newsgroup.

> > > --
> > > Ivan Arjentinski



> > > > SET XACT_ABORT isn't working to specification.

> > > > The following code works fine on 6.5, but orphans transactions in
7.0
> > and
> > > > STILL in 2000

> > > > set xact_abort on
> > > > begin transaction
> > > >  select * from a_table_that_doesnt_exist
> > > >  --exec('select * from a_table_that_doesnt_exist')

> > > > commit
> > > > return
> > > > do_rollback:
> > > > rollback
> > > > return
> > > > go

> > > >  begin
> > > >   print 'rolling back orphaned transaction'
> > > >   rollback
> > > >  end
> > > > go

> > > > If you toggle the exec() version of the select, it works fine, but
I'm
> > not
> > > > happy about having to use Exec() everywhere for a variety of
reasons -
> > eg:
> > > > string construction to include parameters, re-use of execution plans
> > etc,
> > > > etc...

> > > > This is a terrible bug - orphaned transactions might be holding
> > exclusive
> > > > locks on database objects indefinitely - especially where db
> connections
> > > are
> > > > pooled under an app or web server.

> > > > The fact that SET XACT_ABORT works "most" of the time is not good
> enough
> > > > because this bug is deadly enough to bring an app down on its own.


> > > > transaction is orphaned before it gets a chance to run.

> > > > I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
> > > supposed
> > > > to rollback on "any runtime" error. Perhaps this one has snuck in as
a
> > > > result of deferred name resolution in 7.0?

> > > > I read the section on Transact-SQL error handling in Ken Henderson'd
> new
> > > > "Guru's" book (great book Ken) and he suggests the use of Exec() or
> > > nesting
> > > > calls to stored procs (either of which would be particularly *
to
> > > > implement for different reasons). Another idea he has is to check

> > > > solution.

> > > > I'm wondering if anyone else has any other ideas?

> > > > I've loooked hard at this - but it really seems to me to be a bug,
so
> > what
> > > > we REALLY need here is a FIX NOW - another SP if necessary - not
when
> > > Yukon
> > > > comes out and brings in structured exception handling because that
may
> > be
> > > a
> > > > long way off.

> > > > Cheers,
> > > > Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Ken Henderso » Wed, 30 Jan 2002 13:35:54


Hi Greg,

Thanks for the kind words on my new book.  I included the chapter on
Transact-SQL error handling because so many people struggle with it.

While I agree that the error handling in T-SQL doesn't always work as we
might like, you have to understand that something like this wouldn't likely
be fixed in a service pack.  It's just too hairy of a problem.  It would
require fundamental changes to SQL Server -- way beyond the scope of the
typical service pack.

The 208 errors you're listing are just one of several that can cause a batch
to be aborted without ever reaching your error handling code.  Many linked
server errors can also cause a batch to be aborted.  There are others as
well.

As I say in the book, there are some workarounds, but none of them are
particularly pretty.  I guess I'd prefer the sp_executesql workaround to

However, none of these options is foolproof.  Your best option might be
simply to handle the errors in your client-side code when possible.

Good luck,

-kh


> SET XACT_ABORT isn't working to specification.

> The following code works fine on 6.5, but orphans transactions in 7.0 and
> STILL in 2000

> set xact_abort on
> begin transaction
>  select * from a_table_that_doesnt_exist
>  --exec('select * from a_table_that_doesnt_exist')

> commit
> return
> do_rollback:
> rollback
> return
> go

>  begin
>   print 'rolling back orphaned transaction'
>   rollback
>  end
> go

> If you toggle the exec() version of the select, it works fine, but I'm not
> happy about having to use Exec() everywhere for a variety of reasons - eg:
> string construction to include parameters, re-use of execution plans etc,
> etc...

> This is a terrible bug - orphaned transactions might be holding exclusive
> locks on database objects indefinitely - especially where db connections
are
> pooled under an app or web server.

> The fact that SET XACT_ABORT works "most" of the time is not good enough
> because this bug is deadly enough to bring an app down on its own.


> transaction is orphaned before it gets a chance to run.

> I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
supposed
> to rollback on "any runtime" error. Perhaps this one has snuck in as a
> result of deferred name resolution in 7.0?

> I read the section on Transact-SQL error handling in Ken Henderson'd new
> "Guru's" book (great book Ken) and he suggests the use of Exec() or
nesting
> calls to stored procs (either of which would be particularly * to
> implement for different reasons). Another idea he has is to check

> solution.

> I'm wondering if anyone else has any other ideas?

> I've loooked hard at this - but it really seems to me to be a bug, so what
> we REALLY need here is a FIX NOW - another SP if necessary - not when
Yukon
> comes out and brings in structured exception handling because that may be
a
> long way off.

> Cheers,
> Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Greg Linwoo » Wed, 30 Jan 2002 22:26:29


Thanks Ken..

I've looked over the work-arounds you've suggested and it seems to me that

ad-hoc call sounds like a nice solution to me. After all - the thing I'm
most worried about here is the orphaned transactions never being cleaned up
and this would at least take care of this "most" of the time..

The Exec() solution seems like it would be hard to implement, especially in
stored procs with input / output parameters. Nesting stored procs for every
transaction block seems difficult too, although this option may also help
plan optimisation...

I'm really looking forward to some better error handling in SQL Server's
next release!

btw - I'm enjoying and getting much from your new book. I'm not quite
through reading it yet but am really enjoying the balanced practical /
theoretical and anecdotal approach you've taken. It's also great to see Ron
Soukup back in print - even if just for a foreword. There is some really
great stuff in this book - a great investment and worth at least a couple of
reads!

Cheers,
Greg Linwood


> Hi Greg,

> Thanks for the kind words on my new book.  I included the chapter on
> Transact-SQL error handling because so many people struggle with it.

> While I agree that the error handling in T-SQL doesn't always work as we
> might like, you have to understand that something like this wouldn't
likely
> be fixed in a service pack.  It's just too hairy of a problem.  It would
> require fundamental changes to SQL Server -- way beyond the scope of the
> typical service pack.

> The 208 errors you're listing are just one of several that can cause a
batch
> to be aborted without ever reaching your error handling code.  Many linked
> server errors can also cause a batch to be aborted.  There are others as
> well.

> As I say in the book, there are some workarounds, but none of them are
> particularly pretty.  I guess I'd prefer the sp_executesql workaround to
> using EXEC() because of the obvious benefits of plan reuse.  And checking

> However, none of these options is foolproof.  Your best option might be
> simply to handle the errors in your client-side code when possible.

> Good luck,

> -kh



> > SET XACT_ABORT isn't working to specification.

> > The following code works fine on 6.5, but orphans transactions in 7.0
and
> > STILL in 2000

> > set xact_abort on
> > begin transaction
> >  select * from a_table_that_doesnt_exist
> >  --exec('select * from a_table_that_doesnt_exist')

> > commit
> > return
> > do_rollback:
> > rollback
> > return
> > go

> >  begin
> >   print 'rolling back orphaned transaction'
> >   rollback
> >  end
> > go

> > If you toggle the exec() version of the select, it works fine, but I'm
not
> > happy about having to use Exec() everywhere for a variety of reasons -
eg:
> > string construction to include parameters, re-use of execution plans
etc,
> > etc...

> > This is a terrible bug - orphaned transactions might be holding
exclusive
> > locks on database objects indefinitely - especially where db connections
> are
> > pooled under an app or web server.

> > The fact that SET XACT_ABORT works "most" of the time is not good enough
> > because this bug is deadly enough to bring an app down on its own.


> > transaction is orphaned before it gets a chance to run.

> > I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
> supposed
> > to rollback on "any runtime" error. Perhaps this one has snuck in as a
> > result of deferred name resolution in 7.0?

> > I read the section on Transact-SQL error handling in Ken Henderson'd new
> > "Guru's" book (great book Ken) and he suggests the use of Exec() or
> nesting
> > calls to stored procs (either of which would be particularly * to
> > implement for different reasons). Another idea he has is to check

> > solution.

> > I'm wondering if anyone else has any other ideas?

> > I've loooked hard at this - but it really seems to me to be a bug, so
what
> > we REALLY need here is a FIX NOW - another SP if necessary - not when
> Yukon
> > comes out and brings in structured exception handling because that may
be
> a
> > long way off.

> > Cheers,
> > Greg Linwood

 
 
 

SET XACT_ABORT just doesn't work...

Post by Ken Henderso » Thu, 31 Jan 2002 00:00:22


Hi Greg,

Thanks again for your comments on the new book.  I'm glad you like it.  It
always brightens my day when I see people appreciate my work.

Take care,

-kh


> Thanks Ken..

> I've looked over the work-arounds you've suggested and it seems to me that

> ad-hoc call sounds like a nice solution to me. After all - the thing I'm
> most worried about here is the orphaned transactions never being cleaned
up
> and this would at least take care of this "most" of the time..

> The Exec() solution seems like it would be hard to implement, especially
in
> stored procs with input / output parameters. Nesting stored procs for
every
> transaction block seems difficult too, although this option may also help
> plan optimisation...

> I'm really looking forward to some better error handling in SQL Server's
> next release!

> btw - I'm enjoying and getting much from your new book. I'm not quite
> through reading it yet but am really enjoying the balanced practical /
> theoretical and anecdotal approach you've taken. It's also great to see
Ron
> Soukup back in print - even if just for a foreword. There is some really
> great stuff in this book - a great investment and worth at least a couple
of
> reads!

> Cheers,
> Greg Linwood



> > Hi Greg,

> > Thanks for the kind words on my new book.  I included the chapter on
> > Transact-SQL error handling because so many people struggle with it.

> > While I agree that the error handling in T-SQL doesn't always work as we
> > might like, you have to understand that something like this wouldn't
> likely
> > be fixed in a service pack.  It's just too hairy of a problem.  It would
> > require fundamental changes to SQL Server -- way beyond the scope of the
> > typical service pack.

> > The 208 errors you're listing are just one of several that can cause a
> batch
> > to be aborted without ever reaching your error handling code.  Many
linked
> > server errors can also cause a batch to be aborted.  There are others as
> > well.

> > As I say in the book, there are some workarounds, but none of them are
> > particularly pretty.  I guess I'd prefer the sp_executesql workaround to
> > using EXEC() because of the obvious benefits of plan reuse.  And
checking

> > However, none of these options is foolproof.  Your best option might be
> > simply to handle the errors in your client-side code when possible.

> > Good luck,

> > -kh



> > > SET XACT_ABORT isn't working to specification.

> > > The following code works fine on 6.5, but orphans transactions in 7.0
> and
> > > STILL in 2000

> > > set xact_abort on
> > > begin transaction
> > >  select * from a_table_that_doesnt_exist
> > >  --exec('select * from a_table_that_doesnt_exist')

> > > commit
> > > return
> > > do_rollback:
> > > rollback
> > > return
> > > go

> > >  begin
> > >   print 'rolling back orphaned transaction'
> > >   rollback
> > >  end
> > > go

> > > If you toggle the exec() version of the select, it works fine, but I'm
> not
> > > happy about having to use Exec() everywhere for a variety of reasons -
> eg:
> > > string construction to include parameters, re-use of execution plans
> etc,
> > > etc...

> > > This is a terrible bug - orphaned transactions might be holding
> exclusive
> > > locks on database objects indefinitely - especially where db
connections
> > are
> > > pooled under an app or web server.

> > > The fact that SET XACT_ABORT works "most" of the time is not good
enough
> > > because this bug is deadly enough to bring an app down on its own.


> > > transaction is orphaned before it gets a chance to run.

> > > I've scoured BOL and there's no doubt about it - SET XACT_ABORT is
> > supposed
> > > to rollback on "any runtime" error. Perhaps this one has snuck in as a
> > > result of deferred name resolution in 7.0?

> > > I read the section on Transact-SQL error handling in Ken Henderson'd
new
> > > "Guru's" book (great book Ken) and he suggests the use of Exec() or
> > nesting
> > > calls to stored procs (either of which would be particularly * to
> > > implement for different reasons). Another idea he has is to check

> > > solution.

> > > I'm wondering if anyone else has any other ideas?

> > > I've loooked hard at this - but it really seems to me to be a bug, so
> what
> > > we REALLY need here is a FIX NOW - another SP if necessary - not when
> > Yukon
> > > comes out and brings in structured exception handling because that may
> be
> > a
> > > long way off.

> > > Cheers,
> > > Greg Linwood

 
 
 

1. XACT_ABORT doesn't halt transaction on RAISERROR

I am fairly new to error handling on SQL server but familiar with other
databases.

I am trying to add a check after an update in a stored procedure to
make sure the number of rows that was updated was correct.  If not it
should raise an error and rollback the transaction.

I have found out about SET XACT_ABORT ON which is working for me (it is
detecting when I insert duplicate keys etc).

I have also found RAISERROR which is successfully returning errors to
my Delphi App.

My problem is that when I use RAISERROR this does not seem to be
considered a "run-time error" by XACT_ABORT.  The transaction continues
and does not abort.  Is there something I am missing?  Is there another
way I should raise the error?  Is there another procedure I should use
to abort the transaction?  (Calling Rollback will rollback the
transaction so far and then continue from the next statement instead of
aborting the procedure).

Thanks for any help

David

Sent via Deja.com http://www.deja.com/
Before you buy.

2. VFP 5.0 help

3. Scheduling DTS doesn't fail but doesn't work

4. Time out.

5. ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK

6. HELPME PLEASE, How to stop printing.

7. SET DATEFORMAT doesn't work in sp

8. Doing a static search on a web database

9. sp querying a linked server doesn't work although ansi_warnings and ansi_nulls are correctly set

10. Setting default backup folder doesn't work.

11. SQL 2000 set dateformat doesn't work?!

12. SET statement doesn't work within EXEC

13. Set Datestyle doesn't appear to be working