locked
Mixed results RRS feed

  • Question

  • We have a web site (ASP.Net 2.0) running on a windows 2003 server with 8 processors. We have encountered an error that totally baffels me (well, us). When selecting (through stored procedures or "dynamic sql") sometimes the returned result - in the form of a datatable - contains results from a totally different query.

     

    To me the only shared resource between these different queries is the connection pool, fine I though, let's turn off connection pooling and see what happens. When connection pooling is off this error does not occur and as soon as it's turned back on it happens again. To me it seems to have something to do with the fact that the server is a multithreaded environment also running on multiple processors, could there be a bug in the connection pool where the connections are not correctly locked to a reader at a time or is there any other explanation for this?

    Wednesday, September 19, 2007 8:13 AM

All replies

  • Just a thought. Are you guys using static public variables?

    Wednesday, September 19, 2007 1:31 PM
  • No, we're not, there is not shared data at all (well, except for the connection string ofcourse), the connection (SqlConnection) is instantiated as a method-variable and so forth. The no shared data part is ofcourse just partially true since there is shared data but on the .Net framework level, not on application level. The connection pool being the only shared data I can think of.

     

    There's a lot of evidence pointing towards the connection pool, the main thing being that the problem dissapears as soon as we disable connection pooling and returns when we enable it.

    Wednesday, September 19, 2007 2:07 PM
  • I have seen this problem happen with an application that improperly shared a connection across threads.

    Are you using threads or worker processes by chance?

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1943680&SiteID=1

    Wednesday, September 19, 2007 3:39 PM
  • Yes, if you read the question I clearly state that we run in a multi threaded and multi processor environment (well the multi threaded part is implied by the fact that it's asp.net). No, we do not improperly share connections, in fact we do not share connactions at all between threads. The connection in question is instantiated as a METHOD-variable and has no scope beyond the method.

     

    Wednesday, September 19, 2007 4:06 PM
  • When you disable the pool, do you find that a connection open eventually throws an exception after a while rather than the bad data return?

     

    I did read you post.  Saying that your server is multi-processor/multi-threaded really does not help me (at least) know if your application is explicitely threaded.  Saying that yes it is when you mean it is implicitly treaded due to the underpinnings of .net is also not helpful.  I am just some random guy who is attempting to help you brainstorm a cause and solution to your problem, cut me some slack.

     

     

    Wednesday, September 19, 2007 5:02 PM
  • I'm very thankful for you trying to help me, don't get me wrong. I'll give you a code snippet to illustrate a situation where the problem could occur, this is not the actual production code but rather an example, all the premisses are exactly the same though.

     

    Code Snippet

    Public Module Dal

    Private ReadOnly connectionString As String = "whatever"

     

    Private Function GetConnection() As SqlConnection

    Return New SqlConnection(connectionString)

    End Function

     

    Public Function GetPersons(ByVal city As String) As DataTable

    Using connection As SqlConnection = Dal.GetConnection()

    Using command As SqlCommand = connection.CreateCommand()

    command.CommandType = CommandType.StoredProcedure

    command.CommandText = "[People].[GetPersons]"

    command.Parameters.AddWithValue("@city", city)

     

    Dim da As New SqlDataAdapter(command)

    Dim result As New DataTable()

     

    connection.Open()

    da.Fill(result)

    connection.Close()

     

    Return result

    End Using

    End Using

    End Function

    End Module

     

     

    When calling the method GetPersons the resulting DataTable would sometimes contain data from for example the "Products-table" rather than the "Persons-table".

     

    Now, this method could get called from a threaded environment (since it's asp.net it will be since every request gets served on it's own thread) but to me it really shouldn't matter since there is no shared resources except for the ones in the framework.

     

    Also, we've gotten one step further in the investigation, the mix up of results only seems to happen after another, totally unrelated stored procedure has used RAISERROR('Some errormessage', 16, 1); to throw an exception back up to Ado.net. So, it seems somehow that the thrown error somehow corrupts the connection pool, but it only leads to one corrupt reading then the pool seems to magically self heal.

    Thursday, September 20, 2007 8:15 AM