Post reply

set implicit_transactions off permanently?

  • February 17, 2009 at 10:44 am

    #206618

    I'm a noob just trying to gather information on these 3 minute blocked/blocking issues that we've been having for weeks involving our production database and here's a small part of what I've gathered so far...

    I have a third party com object, which I don't have the source code for, that we use to update our production application database. The COM object performs well with one SPID from the webserver running the web application doing all the inserting and updating of the various tables. In looking at a Profiler trace when things are going well, a SQL:BatchStarting/BatchCompleted SET IMPLICIT_TRANSACTIONS OFF shows up right before the INSERT/UPDATE commands and things finish up nicely.

    When things go wrong, a SQL:BatchStarting/BatchCompleted SET IMPLICIT_TRANSACTIONS ON shows up right before the INSERT/UPDATE commands, multiple SPIDs from the same webserver get involved in handling the INSERT/UPDATE commands, I get an X lock on a key blocking an attempt at a S lock on the same key by the two competing SPIDs and things finish up with the web server (a guess on my part) aborting the SPID waiting for the S lock after 3 minutes (with EventSubClass "2 - Rollback", ObjectName "INSERT" and Error "2 - Abort") and then the SPID with the X lock on said key shows SQL:BatchStarting IF @@TRANCOUNT > 0 ROLLBACK TRAN (with with EventSubClass "2 - Rollback" and ObjectName "implicit_transaction") and ends up rolling back as well.

    Not knowing much yet about how the different transaction modes, connections and SPIDs work together, I was wondering if there was an overriding setting for transaction mode per connection or connecting host that I could utilize?

  • September 11, 2011 at 2:58 pm

    #1379741

    Sorry to see no answers came to your post.

    Maybe you are able te tell in you were able to solve the locking problem.

    We are facing similar things.

    We use Sql2005 / delphi ADO

    Thank you in advance,

    Edmond

  • September 11, 2011 at 9:52 pm

    #1379778

    You probably couldn't fix the problem even if you were able to figure out what to do because you don't have the source code to make the right kind of fix.

    This is the 3rd party vendor's fault. Send them an email with as much information as you can/dare and ask them to fix their code. If they refuse, then start looking for a replacement.... perhaps maybe even writing your own code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply