Synchronization of an ODBC connection
The OdbcConnection is not thread-safe in any way, therefor it needs a locking mechanism.

My observation is that the OdbcConnection does send it's received commands down into the Connector. If this is happening in a multi-threaded environment, the sequence of commands is interrupted and you'll receive an exception with this or a similar message:


  ERROR [HY000] [MySQL][ODBC 3.51 Driver]Commands out of sync; you can't run this command now


The driver is confused because the sequence was interrupted. This can not only happen on a OdbcCommand level, but on a OdbcConnection level. The OdbcConnection needs to be locked.

An additional note is that you should lock the OdbcConnection the moment you start to work with a command:

_cmd.Parameters[0].Value = argument;
lock(_conn)
{
    _cmd.ExecuteNonQuery();
}


This is bad, because it could happen in a multi-threaded environment that the parameter gets overridden by another thread before ExecuteNonQuery can be called.

lock(_conn)
{
    _cmd.Parameters[0].Value = argument;
    _cmd.ExecuteNonQuery();
}


This keeps the execution as one atomic operation which can't be interrupted.

Further investigations revealed that the MySQL Odbc-Connector has a locking mechanism...but only on a statement level:


  MySQL ODBC Connector, 5.1.9, execute.c


 39:    SQLRETURN do_query(STMT FAR *stmt,char *query, SQLULEN query_length)
 40:    {
 ...
 55:        MYLOG_QUERY(stmt, query);
 56:        pthread_mutex_lock(&amp;stmt-&gt;dbc-&gt;lock);
 ...
 96:        exit:
 97:        pthread_mutex_unlock(&amp;stmt-&gt;dbc-&gt;lock);
 ...
114:   }


And to make it clear: If you have a non-thread-safe connection, lock the whole connection.

And to make it further clear, using an OdbcDataReader counts as using the connection. As long as you work with an OdbcDataReader, you should keep the lock on the connection. 

This includes disposing of the resources, f.e. a DataReader. You should always use using inside the locked parts to make sure that the resources are freed again, otherwise it could happen that a DataReader lingers on and is later destroyed by the Garbage Collector and a rather inconvenient moment, like this:

Thread  Action
     1  Lock Connection
     1  Create Statement
     2  Lock Connection -&gt; Wait
     1  Prepare Statement
     1  Execute Statement
     1  Get Reader
     1  Use Reader
     1  Unlock Connection
     2  Lock Connection
     2  Prepare Statement
     2  Execute Statement
    GC  Destroy Statement from thread #1
    GC  Destroy Reader from thread #1


In code it would like this:

lock (connection)
{
    IDbCommand command = connection.CreateCommand();
    command.CommandText = "SQL";
    IDataReader reader = command.ExecuteReader();
    // Do something the reader
    return valuesFromTheReader;
}


This is bad as it allows resources to linger on and the GC will later destroy them, with possible disastrous results. My tests revealed that from the exception


  Attempted to read or write protected memory.


to a simple deadlock of the application on an arbitrary line1 everything is possible if you fail to correctly synchronize database access.

The best pattern is to always destroy everything while in inside the lock:

lock (connection)
{
    using (IDbCommand command = connection.CreateCommand())
    {
        command.CommandText = "SQL";
        using (IDataReader reader = command.ExecuteReader())
        {
            // Do something the reader
            return valuesFromTheReader;
        }
    }
}




1: I'm not kidding, I could reliable reproduce a hang on the following instruction inside the System.Data.Odbc.OdbcDataReader.NextResult(bool, bool)

IL_0099: ldarg.1
00000099 8B F8 mov edi,eax


when I did not properly dispose of a DataReader.