none
Implicit Transaction for Stored Procedure Calls? RRS feed

  • Question

  • I know that an implicit transaction gets made for submits but does one also get made for calls to stored procedures?

    I've been having problems with timeout issues on a stored procedure that normally takes less than a second to run.  The timeout errors tell me about the data being put in and, of the several dozen I've checked, every single one of them is of a type that causes my stored procedure to have to call a linked server to complete.  So I put in some quick code in the stored procedure to track down each one by inserting a row into a debug table and updating some date columns as it goes along.  But when I look at the table after a spat of errors, there isn't a row for the problem data!  I haven't purposefully put in any transactions so I'm wondering if something else is that causes it to remove the row when the timeout hits.

    The call is in the form of: Dim R = MyContext.MyProc(MyParam).Single.  ObjectTrackingEnabled has been turned off and this is the only call made on this instance of the data context.  And, naturally, the issue only happens in production.

    Thursday, May 4, 2017 3:47 PM

Answers

  • Hi Zloth X,

    >>I know that an implicit transaction gets made for submits but does one also get made for calls to stored procedures?

    As I know, when it call stored procedures, it does not use submit method. So I think that does not implicitly use transactions. you could use transaction in your stored procedure.

    >>naturally, the issue only happens in production.

    Please implement the business logic by using c# instead of stored procedure and check if what step causes the issue.

    Best regards,

    Cole Wu


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Marked as answer by Zloth X Friday, May 5, 2017 4:58 PM
    Friday, May 5, 2017 5:09 AM
    Moderator

All replies

  • Hi Zloth X,

    >>I know that an implicit transaction gets made for submits but does one also get made for calls to stored procedures?

    As I know, when it call stored procedures, it does not use submit method. So I think that does not implicitly use transactions. you could use transaction in your stored procedure.

    >>naturally, the issue only happens in production.

    Please implement the business logic by using c# instead of stored procedure and check if what step causes the issue.

    Best regards,

    Cole Wu


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Marked as answer by Zloth X Friday, May 5, 2017 4:58 PM
    Friday, May 5, 2017 5:09 AM
    Moderator
  • Well, we're going to change that one routine to call the old fashioned way and see if that kicks something loose.

    Use C#?  You mean make a CLR and call that?  Yeah, that sounds like a good idea.  At the very least it would open up far more logging opportunities.  We'll try that out next in the (pretty likely) case that our current fix doesn't help.  Thanks!

    Friday, May 5, 2017 4:57 PM
  • Changing to the old fashioned method fixed the issue.  I don't really like it when an issue I don't understand gets fixed by a shot in the dark like this but it's better than having batches of errors appear.

    Friday, June 2, 2017 9:01 PM