MySQL Bugs: #59886: ExecuteNonQuery is not thread safe

Bug #59886	ExecuteNonQuery is not thread safe
Submitted:	2 Feb 2011 10:23	Modified:	19 Feb 2011 12:03
Reporter:	Jalal Chaer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.6	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any

Description:
Excute Non Query is not thread safe.... 
This is related to a more serious problem with DataReader which I'll send in another bug report ... 

Thanks

How to repeat:
[Test]
        public void ExecuteNonQuery_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();
                }
            }
            finally
            {
                foreach (var thread in threads)
                {
                    thread.Join();
                }
            }
        }

Suggested fix:
In command.cs

 add this:
        private static object lockObject = new object();

and change this:
 public override int ExecuteNonQuery()
        {
            lock (lockObject) // add this
            {
                using (MySqlDataReader reader = ExecuteReader())
                {
                    reader.Close();
                    return reader.RecordsAffected;
                }
            }
        }

I need more information.  Can you show me where/how you are creating the connection object you are using to execute this sql?

Hello,
Simply add this test to your test suit ... and run it. It uses the base test methods.

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.

Thanks for considering this critical issue.

I don't agree with the "lock penalty" thing. I believe, consistency is way more important than performance. If it won't work, why I need it to work fast ??

Thread-safety means well-behaving code even in multi-threaded environments which is the "standard" for the time being.

As a service, the MySql connector should act normally in such environment.