There is already an open DataReader ... even though it is not

Question

Note: I've went through millions of questions when the issue is not disposing the reader/connection properly, or when the error is because of badly handled lazy loading. I believe that this issue is a different one, and probably related to MySQL's .NET connector.

I'm using MySQL server (5.6) database extensively through its .NET connector (6.8.3). All tables are created with MyISAM engine for performance reasons. I have only one process with one thread (update: in fact, it's not true, see below) accessing the DB sequentially, so there is no need for transactions and concurrency.

Today, after many hours of processing the following piece of code:

public IEnumerable<VectorTransition> FindWithSourceVector(double[] sourceVector)
{
    var sqlConnection = this.connectionPool.Take();

    this.selectWithSourceVectorCommand.Connection = sqlConnection;

    this.selectWithSourceVectorCommand.Parameters["@epsilon"].Value
        = this.epsilonEstimator.Epsilon.Min() / 10;

    for (int d = 0; d < this.dimensionality; ++d)
    {
        this.selectWithSourceVectorCommand.Parameters["@source_" + d.ToString()]
        .Value = sourceVector[d];
    }

    // *** the following line (201) throws the exception presented below
    using (var reader = this.selectWithSourceVectorCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            yield return ReaderToVectorTransition(reader);
        }
    }

    this.connectionPool.Putback(sqlConnection);
}

threw the following exception:

MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

Here is the relevant part of the stack trace:

at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception) at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex) at MySql.Data.MySqlClient.MySqlCommand.CheckState() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at implementation.VectorTransitionsMySqlTable.d__27.MoveNext() in C:\Users\bartoszp...\implementation\VectorTransitionsMySqlTable.cs:line 201

at System.Linq.Enumerable.d__3a1.MoveNext() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) at implementation.VectorTransitionService.Add(VectorTransition vectorTransition) in C:\Users\bartoszp...\implementation\VectorTransitionService.cs:line 38

at Program.Go[T](Environment`2 p, Space parentSpace, EpsilonEstimator epsilonEstimator, ThresholdEstimator thresholdEstimator, TransitionTransformer transitionTransformer, AmbiguityCalculator ac, VectorTransitionsTableFactory vttf, AxesTableFactory atf, NeighbourhoodsTableFactory ntf, AmbiguitySamplesTableFactory astf, AmbiguitySampleMatchesTableFactory asmtf, MySqlConnectionPool connectionPool, Boolean rejectDuplicates, Boolean addNew) in C:\Users\bartoszp...\Program.cs:line 323

The connectionPool.Take returns the first connection that satisfies the following predicate:

private bool IsAvailable(MySqlConnection connection)
{
    var result = false;

    try
    {
        if (connection != null
            && connection.State == System.Data.ConnectionState.Open)
        {
            result = connection.Ping();
        }
    }
    catch (Exception e)
    {
        Console.WriteLine("Ping exception: " + e.Message);
    }

    return result && connection.State == System.Data.ConnectionState.Open;
}

(This is related to my previous question, when I resolved a different, but similar issue: MySQL fatal error during information_schema query (software caused connection abort))

The FindWithSourceVector method is called by the following piece of code:

var existing
    = this.vectorTransitionsTable
        .FindWithSourceVector(vectorTransition.SourceVector)
        .Take(2)
        .ToArray();

(I need to find at most two duplicate vectors) - this is the VectorTransitionService.cs:line 38 part of the stack trace.

Now the most interesting part: when the debugger stopped execution after the exception occured, I've investigated the sqlConnection object to find, that it doesn't have a reader associated with it (picture below)!

Why is this happening (apparently at "random" - this method was being called almost every minute for the last ~20h)? Can I avoid that (in ways other then guess-adding some sleeps when Ping throws an exception and praying it'll help)?

---

Additional information regarding the implementation of the connection pool:

Get is intended for methods that call only simple queries and are not using readers, so the returned connection can be used in a re-entrant way. It is not used directly in this example (because of the reader involved):

public MySqlConnection Get()
{
    var result = this.connections.FirstOrDefault(IsAvailable);

    if (result == null)
    {
        Reconnect();

        result = this.connections.FirstOrDefault(IsAvailable);
    }

    return result;
}

The Reconnect method just iterates though the whole array and recreates and opens the connections.

Take uses Get but also removes the returned connection from the list of available connections so in case of some methods that during their usage of a reader call other methods that also need a connection, it will not be shared. This is also not the case here, as the FindSourceVector method is simple (doesn't call other methods that use the DB). However, the Take is used for the sake of convention - if there is a reader, use Take:

public MySqlConnection Take()
{
    var result = this.Get();

    var index = Array.IndexOf(this.connections, result);

    this.connections[index] = null;

    return result;
}

Putback just puts a connection to the first empty spot, or just forgets about it if the connection pool is full:

public void Putback(MySqlConnection mySqlConnection)
{
    int index = Array.IndexOf(this.connections, null);

    if (index >= 0)
    {
        this.connections[index] = mySqlConnection;
    }
    else if (mySqlConnection != null)
    {
        mySqlConnection.Close();
        mySqlConnection.Dispose();
    }
}

Answer 1

I suspect this is the problem, at the end of the method:

this.connectionPool.Putback(sqlConnection);

You're only taking two elements from the iterator - so you never complete the while loop unless there's actually only one value returned from the reader. Now you're using LINQ, which will automatically be calling Dispose() on the iterator, so your using statement will still be disposing of the reader - but you're not putting the connection back in the pool. If you do that in a finally block, I think you'll be okay:

var sqlConnection = this.connectionPool.Take();
try
{
    // Other stuff here...

    using (var reader = this.selectWithSourceVectorCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            yield return ReaderToVectorTransition(reader);
        }
    }
}
finally
{
    this.connectionPool.Putback(sqlConnection);
}

Or ideally, if your connection pool is your own implementation, make Take return something which implements IDisposable and returns the connection back to the pool when it's done.

Here's a short but complete program to demonstrate what's going on, without any actual databases involved:

using System;
using System.Collections.Generic;
using System.Linq;

class DummyReader : IDisposable
{
    private readonly int limit;
    private int count = -1;
    public int Count { get { return count; } }

    public DummyReader(int limit)
    {
        this.limit = limit;
    }

    public bool Read()
    {
        count++;
        return count < limit;
    }

    public void Dispose()
    {
        Console.WriteLine("DummyReader.Dispose()");
    }
}

class Test
{    
    static IEnumerable<int> FindValues(int valuesInReader)
    {
        Console.WriteLine("Take from the pool");

        using (var reader = new DummyReader(valuesInReader))
        {
            while (reader.Read())
            {
                yield return reader.Count;
            }
        }
        Console.WriteLine("Put back in the pool");
    }

    static void Main()
    {
        var data = FindValues(2).Take(2).ToArray();
        Console.WriteLine(string.Join(",", data));
    }
}

As written - modelling the situation with the reader only finding two values - the output is:

Take from the pool
DummyReader.Dispose()
0,1

Note that the reader is disposed, but we never get as far as returning anything from the pool. If you change Main to model the situation where the reader only has one value, like this:

var data = FindValues(1).Take(2).ToArray();

Then we get all the way through the while loop, so the output changes:

Take from the pool
DummyReader.Dispose()
Put back in the pool
0

I suggest you copy my program and experiment with it. Make sure you understand everything about what's going on... then you can apply it to your own code. You might want to read my article on iterator block implementation details too.

Answer 2

TyCobb and Jon Skeet have correctly guessed, that the problem was the pool implementation and multi-threading. I forgot that actually I did start some tiny Tasks in the Reconnect method. The first connection was created and opened synchronously but all other where opened asynchronously.

The idea was that because I only need one connection at time, there others can reconnect in different threads. However, because I didn't always put the connection back (as explained in Jon's answer) reconnecting was happening quite frequently, and because the system was quite loaded these reconnection threads weren't fast enough, which eventually led to race conditions. The fix is to reconnect in a more simple and straightforward manner:

private void Reconnect()
{
    for (int i = 0; i < connections.Length; ++i)
    {
        if (!IsAvailable(this.connections[i]))
        {
            this.ReconnectAt(i);
        }
    }
}

private void ReconnectAt(int index)
{
    try
    {
        this.connections[index] = new MySqlConnection(this.connectionString);
        this.connections[index].Open();
    }
    catch (MySqlException mse)
    {
        Console.WriteLine("Reconnect error: " + mse.Message);
        this.connections[index] = null;
    }
}