EF4/SQL Server 2008 R2 - "The underlying provider failed on EnlistTransaction." / "The operation failed because the session is not single threaded." (SqlClient Data Provider) RRS feed

  • Question

  • This is thrown as a System.Data.EntityException, with an inner System.Data.SqlClient.SqlException with error messages as quoted in the subject.

    Exactly, what triggers this error message?

    I am pretty comfortable with both Entity Framework and multi threading, and I understand the litteral meaning. But digging deeper, I've done some searches and found very little to figure out what criterias cause this exception and how it can be avoided.

    Is there somebody who can shed some light on this?

    ps. Context for when this happens is described in a separate post here.

    • Edited by M. Hol Wednesday, April 4, 2012 12:39 PM
    Wednesday, April 4, 2012 12:37 PM

All replies

  • hi,

    i think it shas something to do with having MARS enabled on your connection:

    if found this article on that: http://blogs.msdn.com/b/cbiyikoglu/archive/2006/11/21/mars-transactions-and-sql-error-3997-3988-or-3983.aspx

    Regards, Nico

    Thursday, April 5, 2012 8:44 AM
  • Hi,

    sorry, i didn't realize that Patrice Scribe also mentionned the same article.

    I must say i strongly agree with Patrice; whenever batch processing is mentionned, first priority should always be to bring it as close as possible to the server. The performance hit by pulling it out of the server, calculating, and putting it back is HUGE, believe me, i've seen it many times: what took hours to run, sometimes was reduced to seconds. It is especially true if you can keep the calculation in transact sql, in a set-oriented way, i.e. without having loops, cursors, or if-then-elses. Many programmers look down on sql as being a weird kind of 'not even a real language', because they don't realize its potential. I have yet to encounter the first procedural algoritm that i can't do in a set oriented way, and i can assure you that I have done thousands of them. 

    Working in a set oriented way, also allows the sql optimizer to spread the work over more than one thread.

    What you dreamed up there looks good on paper, but these scenarios only will work if you can off-load the queue on another server, in an asynchronous way (i.e. the WS call should not wait for the worker threads to end).

    Another solution that i once used is sending webservice-calls in a ONE-WAY fashion (it's an attribute that you can set at webmethod level); the web service caller sends a request with a guid and doesn't wait for an answer. With another call its polls if the job (identified with the gui) is finished, and if so, get the result. That way, you use the webserver as background-worker-thread server.

    Regards, Nico

    • Marked as answer by Allen_MSDN Tuesday, April 10, 2012 2:40 AM
    • Unmarked as answer by M. Hol Tuesday, April 10, 2012 12:49 PM
    Friday, April 6, 2012 9:15 AM