Bug #59887 ExecuteReader is not thread safe

• Submitted: 2 Feb 2011 10:27
• Modified: 18 Feb 2011 20:47
• Reporter: Jalal Chaer
• Status: Not a Bug
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 6.3.6
• OS: Windows (7)
• CPU Architecture: Any

[2 Feb 2011 10:27] Jalal Chaer

Description:
This is based upon fixing http://bugs.mysql.com/59886

I'm experiencing alot of :

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

This happens in whenever 2 or more threads try to access the MySql server ...

I'm trying to repeat it with the following test, but I think you might come with a better solution to this critical problem

How to repeat:
      [Test]
        public void ExecuteReader_is_not_thread_safe()
        {
            execSQL("CREATE TABLE Test (name Varchar(50))");

            var threads = new List<Thread>();
            try
            {
                for (var i = 0; i < 10; i++)
                {
                    var thread = new Thread(() =>
                    {
                        var sql = string.Format("INSERT INTO Test VALUES ('Name {0}')", i);
                        execSQL(sql);
                    });
                    threads.Add(thread);
                    thread.Start();
                }

                var stop = false;
                do
                {

                    using (var reader = execReader("SELECT * FROM Test"))
                    {
                        reader.Read();
                        reader.Close();
                    }

                    if (stop)
                        break;
                    if (threads.All(x => !x.IsAlive))
                        stop = true;
                } while (true);

            }
            finally
            {
                foreach (var thread in threads)
                {
                    thread.Join();
                }
            }
        }

Suggested fix:
No Idea ... I tried but something is not looking good.

[2 Feb 2011 11:02] Jalal Chaer

Possible Fix:
==============
1 possible fix is to let the second wait until the first call, on the same connection, is done ... rather than throwing exceptions .. 

something like this: 

         public new MySqlDataReader ExecuteReader(CommandBehavior behavior)
        {

            //    var stop = false;
            //    do
            //    {
            //        if (stop)
            //            break;
            //        if (connection.Reader == null)
            //            stop = true;

            //    } while (true);

           // The rest of the method ....

[18 Feb 2011 20:47] Reggie Burnett

This is not a bug.  Our implementation of ExecuteNonQuery and ExecuteScalar use ExecuteReader internally and so "look" like ExecuteReader calls.  It does appear that SqlClient allows parallel execution of ExecuteNonQuery (and we presume ExecuteScalar) although they make it a point to indicate that those methods are not guaranteed to be thread-safe.  

We've created an internal feature request to re-evaluate the thread safety of these methods.  However, given that even on SqlClient those methods are not sure to be thread safe, the only logical thing to do in your case would be to use a lock structure in your code wrapped around calls to the connector.  That way, the lock penalty is only paid by people who need parallel execution.