none
ADO returns result set from preceeding SQL query RRS feed

  • Question

  • Hi,  I’m hoping someone can help me with a problem I’m having.

    To summarise: randomly,  ADO calls return the same result set that was returned to the preceeding SQL call on the connection.

     

    Has anyone seen this problem before? Or have any idea what might be causing this or how to fix it?

     

    More detail:

    I have the following code:

    [code]

    using (var cmd = Connection.CreateCommand())

    {

    Connection.Open();

          cmd.CommandType = CommandType .Text;

          cmd.CommandText = "INSERT INTO AssemblyBinaries (AssemblyData) VALUES (@AssemblyData); SELECT @@IDENTITY AS AssemblyId" ;

          cmd.Parameters.Add(new SqlParameter ("@AssemblyData" , SqlDbType .Image, 2147483647, ParameterDirection .Input, true , 0, 0, "AssemblyData" , DataRowVersion .Current, assemblyBinary.AssemblyData));

          using (IDataReader reader = cmd.ExecuteReader())

          {

              reader.Read();

              etc...

    [/code]

     

    However, when I run this code, instead of reader having a result set with a single column named “AssemblyId”, sometimes it would have an entirely different result set.

    Further investigation showed the result set that was returned looked like the results of the previous SQL query on the connection.

    e.g. The result set contained four columns “VersionMajor”, “VersionMinor”, “BuildNumber” and “DatabaseType”.  Looking at the log from the SQL Server profiler, I looked for the “Insert into AssemblyBinaries...” query.  Checking the connection, I see that the prior call on that connection was “SELECT VersionMajor, VersionMinor, BuildNumber, DatabaseType FROM DatabaseVersion”.

     

    A timing of the events is also interesting.

    In the SQL Server Profiler log, the “Select VersionMajor...” query started at 15:13:51.010 and finished at 15:13:51.057

    The “Insert into AssemblyBinaries...” query started at 15:14:35.143 and finished at 15:14:49.047

    However, the C# client log shows reader.Read() returned at 15:14:35.  That is, immediately after the SQL server started the query, but long before the SQL server finished processing the query.  (I therefore conclude the problem isn’t in the SQL server.  Which leaves the ADO stack on the client as the culprit.)

     

    Additional notes.

    1. <!-- [if !supportLists]--> <!-- [endif]-->ADO is using connection pooling.  When it’s turned off, the problem stops.
    2. <!-- [if !supportLists]--> <!-- [endif]-->Multiple Active Result Sets isn’t a factor.  Problem occurs even when it’s explicitly turned off, although most MS docs say it’s off by default. (There are other docs that say it’s on by default.)
    3. <!-- [if !supportLists]--> <!-- [endif]-->The problem occurs more frequently on a dual core VM than a single core.
    4. <!-- [if !supportLists]--> <!-- [endif]-->This problem occurs randomly, sometimes the calls work, and sometimes they don’t.
    5. <!-- [if !supportLists]--> <!-- [endif]-->I’ve tried turning on ADO logging (via ETW) but it didn’t show anything useful.
    6. I'm using SQL Server 2005.
    7. The application under test is running under Windows 2003, in a VMWare virtual machine.
    8. The code is multithreaded.

     

    • Edited by dteviot Wednesday, January 20, 2010 2:50 AM removed garbage inserted during cut and paste
    • Moved by eryang Wednesday, January 20, 2010 6:12 AM wrong forum. (From:.NET Base Class Library)
    Wednesday, January 20, 2010 2:49 AM

Answers

  • Just in case anyone's interested, it WAS a multithreading bug.  Two threads tried to use the same connection simultaneously and corrupted it.
    • Marked as answer by dteviot Monday, February 22, 2010 12:52 AM
    Monday, February 22, 2010 12:52 AM

All replies

  • You should be using ExecuteNonQuery instead of ExecuteReader when running an INSERT statement and returning an identity column value. The below link has an example of how to implement this:

    http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 20, 2010 1:20 PM
  • Paul,

    I think I wasn't clear enough in stating my problem, which is I'm making queries, and getting result sets back that copies of the preceeding query.  The above was just one example where the code was simple.
    I also have cases where I'm calling a stored procedure and require a result set back (and get the wrong one.) And others where the stored proc does not return a result set, but instead returns it's result via an output parameter.  They also exhibit this problem.

    I believe the problem resides in the ADO.NET stack (or the way I'm using it.)  My Google searches failed to find anything, and I was hoping someone here had seen the problem before and could tell me what I'm doing wrong with ADO.NET.  (Possibly I'm using an older version where it's a known bug?)
    Friday, January 22, 2010 12:16 AM
  • If this is an intermittent problem perhaps it's an issue with your multi-threaded code. Have you ruled that out?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, January 22, 2010 3:54 AM
  • I agree, the fact that it's intermittant, and appears more often on a multi-core image suggests it's a multi-threading bug.  I have checked the code, and I can't see anything obviously wrong with the threading.  (That said, it's almost impossible to be certain that the multi-threading code is correct.)
    Sunday, January 24, 2010 8:27 PM
  • Can anyone confirm that when using the SQL Server classes in ADO.NET 2.0, ADO and SQL Native Client (SNC) components are not used?
    I've run reflector on System.Data.DLL, and it clearly shows the TDS part of the protocol is implemented in the DLL itself.  Unfortunately, because the transport level functions such as  SNIWriteSync(),  Np::WriteSync() and Np::ReadSync() are native code, it's not easy to tell if they call down into either ADO or SNC dlls.

    Specifically, what I'm trying to check/prove is that when using the SQL Server classes in ADO.NET 2.0, the components used in the ADO.NET stack used by a SQL client are entirely determined by the version of the .NET framework installed and the OS DLLs.  The version of ADO, SNC, MDAC or SQL server installed on the PC is irrelevant.

    Thanks.
    • Edited by dteviot Tuesday, January 26, 2010 9:13 PM SCN is _SQL_ native client
    Tuesday, January 26, 2010 9:12 PM
  • The SQL Native Client is only available via System.Data.OleDb and System.Data.Odbc. If you're using the native .NET SQL Provider (System.Data.SqlClient) the SQL Native Client is not required.

    I believe the version loaded is determined by that which was used when your app was compiled and the version of .NET that is installed on the system.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, January 27, 2010 3:21 AM
  • OK, I just had another look at the ETW logging, and it's possible that my previous claim that it doesn't show anything useful may have been wrong.
    Of note, you can can see there are two Connection.Open() and Connection.Close() paths.
    There's the case where a Connection is "closed" by putting it back into the pool (and is available for reuse), and the case where (I believe) the connection is actually closed.
    These cases look like (respectively):
    [code]
    "enter_01 <sc.SqlConnection.Close|API> 2#"
    "leave_01"
    [/code]
    and
    [code]
    "enter_01 <sc.SqlConnection.Close|API> 2#"
    ... tens of lines of code, including such things as
    "<SNIClose|RET|SNI> 0{WINERR}"
    ...
    "leave_01"
    [/code]

    There are similar code for a "pooled" open, and an actual open.  e.g.
    [code]
    "enter_01 <sc.SqlConnection.Open|API> 2#"
    "leave_01"
    [/code]
    and
    [code]
    "enter_01 <sc.SqlConnection.Open|API> 15#"
    "<sc.TdsParser.Connect|SEC> SQL authentication"
    "enter_02 <SNIOpenSyncEx|API|SNI> pConsumerInfo: 0A5CE46C  wszConnect: 'NODE-2K3-SP2-02\SQL2K8'  ppConn: 0A5CE494  fIntSec: 0{BOOL}  cbBuffer: 0  pBuffer: 00000000  pInstanceName: 08E59C90  fOverrideCache: 0{BOOL}  fSync: 1{BOOL}  timeout: -1"
    ... dozens of additional lines
    "leave_01"
    [/code]

    Now, what I noticed was that under the failure case, the connection that is used for the failed call was fully closed after the previoius call, but when it was opened for the AssemblyInsert() call (the call that failed) it's only opened using the "pooled" open, not the full open.  (Comparing with a successful run, the connection was opened using the full open sequence.)


    • Edited by dteviot Wednesday, January 27, 2010 9:35 PM hopefully made formatting better
    Wednesday, January 27, 2010 9:34 PM
  • Just in case anyone's interested, it WAS a multithreading bug.  Two threads tried to use the same connection simultaneously and corrupted it.
    • Marked as answer by dteviot Monday, February 22, 2010 12:52 AM
    Monday, February 22, 2010 12:52 AM
  • Yes, I believe that was something I suggested you check.

    Glad you worked it out. :-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, February 22, 2010 3:11 AM
  • dteviot,

     

    I am having the same issue with an application I have recently inherited.  Can you give me some guidance on how you tracked down the issue like what the bad code was you found or methods used to isolate the issue.  I can't reproduce the issue in-house and it only occurs intermittently.  Code diving is proving useless as the application is rather large.

     

    Thanks,

    Dloz76

    Thursday, November 4, 2010 3:30 PM
  • Well, I'm not sure how much use this is to you but I solved it by having an automated test that was constantly running, and able to reproduce the bug two or three times a day.

    So it would run, check for failure, and if it failed, signal me.  I'd then go crawling through the output/debugging logs looking for anything.  If the bug didn't appear, it would re-run the test.  Rinse, repeat.

    Anyway, after about 6 weeks of examining the debug logs, I noticed some output that didn't look quite correct. When checked further, it showed that some code was not correctly threadsafe.  I changed the code so it was safe and reran the tests for a week, with the bug not showing.

    The main things done to fix the problem were:

    1. All DB connection operations were of the form: Open, do work, close.  With this sequence guarded with a lock, so only one thread ever used the connection.

    2. Where two threads needed to talk to the database, they each opened their own connection, there was no attempt to reuse connections.

    3. According to MS Docs, provided you're using SQL 2k5 or above, relying on connection pooling and opening and closing connections is just as fast as opening a connection and reusing it.  (My quick and dirty experiments confirmed this.)

    Tuesday, December 14, 2010 2:56 AM