3

Is there a way trap error 266 inside JDBC or inside SQL by wrapping an exec statement in some way?

We are looking to develop a guard around stored procedures exiting with a transaction left open in a common way. This helps guard against programmer error that can cause significant issues when combined with connection pooling.

Scott Markwell
  • 1,091
  • 2
  • 16
  • 33
  • wouldn't it be easier (and make more sense) to provide a template for stored procs? This could then be quickly code reviewed for correctness... – Mitch Wheat Nov 02 '10 at 23:48
  • That already is done, including standard error handling, but since SQL Server 2005 has no concept of try/catch/finally, just try/catch, it is possible to inject logic that simply returns in the middle of the transaction leaving a transaction open. This we need a better solution to detect and monitor these type of errors. – Scott Markwell Nov 02 '10 at 23:50
  • @Scott Markwell: The finally is effectively just after "END CATCH"! – gbn Nov 03 '10 at 05:44
  • @gbn, there are code escapes possible that won't run the code immediately after the catch. Where in a true try/catch/finally pattern it would be run. – Scott Markwell Nov 04 '10 at 15:23
  • @Scott Markwell: not if TRY/CATCH is used: any error that fails to go to the CATCH block means the code never ran or compiled, pretty much – gbn Nov 04 '10 at 15:37
  • If you stick a return in the middle of a try, the catch block nor any code after is run, in the try/catch/finally pattern, the finally block is always run, no matter the jump point. – Scott Markwell Nov 04 '10 at 16:20

2 Answers2

3

Yes. Use SET XACT_ABORT ON.

It suppresses error 266 and also forces a rollback in any circumstances, including client command timeouts which is simply an abort.

This is separate to the points made by PerformanceDBA which are mostly valid.

Other links:

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Some very interesting material, and provides some avenues to explore, but setting XACT_ABORT ON doesn't provide a means of trapping 266, there are code situations due to programmer error that will not produce something to trigger XACT_ABORT to rollback, but still trigger 266. – Scott Markwell Nov 04 '10 at 15:33
  • This is why you shozuld have a proc template :-) You are correct though, SET XACT_ABORT ON suppresses 266 which would occur otherwise. – gbn Nov 04 '10 at 15:40
1

  1. The lower numbered errors, 15000 and below IIRC, and the higher severity levels, interrupt execution. That means the entire thread is interrupted, there is no possibility to trap it inside stored proc code.

  2. Anyway, the errors are beyond the control of SQL code or stored procs. The lower numbered errors are "hard" errors, there is nothing you can do about it, even if you did trap it. It is within the servers (not your) domain. Eg. hard disk error; 1205. The server decides, the thread cannot continue, and terminates the spid.

  3. It is not feasible to trap that outer domain of errors which you did not administer, set up, or control. So I cannot understand the basis for you expecting that you can. the only way to trapa (eg) hardware errors and deadlocks, is if you wrote your own server.

  4. But the most important thing is this. you are breaking basic laws of transaction control (which IS within your power) by processing user interaction while a transaction is open. The rule (unchanged for 40 years) is:

    • perform all user interaction with no transaction open
    • when that is complete, terminate further user interaction
    • begin transaction,
    • perform all your DML, and
    • commit
      .

  5. Failure to obey these rules will lead to various problems which are easy to prevent (by compliance with the rule):

    • uncontrolled lock duration
    • hung transactions (waiting for users who have gone to lunch)
    • very slow response, and the server is idle, waiting for locks
      .
      If you are driving a car, sure, you can fiddle around on suburban streets and shopping malls, but once you get one the freeway, you cannot drive less than the speed limit; you cannot stop and wait for passengers. If you do, you will hang everyone up. Trying to trap the cell phone call that will cause the police to come after you, or trying to stop the police from dragging you off, is well, somewhat beyond your powers, mighty as they may be.
      .

  6. In the normal circumstance, when the server experiences a hard error, and it cancels your spid (which of course includes a rollback), the issue is closed. The behaviour as per ANSI SQL 89 (predating 92) compliance. Transactions are Atomic, and Durable. Now if you do not code for that model (suppliying the Isolation and Consistency), it aint no "transaction", it is merely a string of uncontrolled SQL spread across time and space, easily interruptible; fragile and vulnerable.

    Obviously that means NO AUTOCOMMIT and NO CHAINED, because it breaches the ANSI SQL standard. It is very sad that (a) MS provide such a sick feature, and (b) people use it, without understanding the danger and the non-compliance.

  7. Instead of concerning yourself with what you think the server should and should not do (which is futile, because it ain't gonna change), I would suggest you take responsibility for the code you produce, and educate yourself re how the server actually works, what it actually does, before designing and writing any code.

  8. Put in your terms, oh it definitely performs try/catch/finally, and half the errors it catches cause it to blow you away, so your try/catch never gets to execute. The concept of your SQL code attempting finally does not exist. SQL is a high level database manipulation language, not a low level language which can control the hardware resources.

    If you are driving a car, on the freeway, you cannot trap (a) the bridge ten kilometres in front of you crashing down or (b) the police closing the freeway one kilometre in front of you

    Attempting to control the uncontrollable is of course ...

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • You assume that humans, who write code, are infallible. I can't make that assumption. Humans write the code, review the code, package it for deployment. All the use cases that have been analyzed indicated that the appropriate course of action in such a situation is to rollback the hang transaction, which would've been done anyways if connection pooling was not enabled. I am not attempting to guard against the server itself, but guard against human error. There are multiple ways to mitigate human error, this is one of them. – Scott Markwell Nov 04 '10 at 15:19
  • To make myself a little bit more clear, Error 266 does not rollback the transaction, which is what is wished. – Scott Markwell Nov 04 '10 at 15:31
  • On the contrary, humans, particularly SQL programmers, are fallible; that's why the server has to catch errors beyond the pgmrs control, etc. and deal with it. That's the gist of my post. I still say, you have a pgmg problem, in that you have left an xact open, when the connection gets closed, and implementing what I detailed is the formal method of guarding against pgmr error. Mixed un/chained simply does not work. MS doesn't do a lot of things that it should. – PerformanceDBA Nov 06 '10 at 05:29
  • An additional wrinkle is that the application engine talking to the database makes use of Connection pooling, there for, an open transaction causes havoc down the line on disparate code paths also causing massive database locking and transactional rollbacks. – Scott Markwell Nov 17 '10 at 19:32
  • 1
    @Scott: That's one of the categories I was posting about. The hardcore fix may be to add this at the end of every transactional code segment: IF @@TRANCOUNT != 0 ROLLBACK TRAN. The better method is to improve your error checking and code blocks within all transactions. – PerformanceDBA Nov 20 '10 at 06:03