After a recent upgrade our rather high volume Python application server started sometimes getting the “Commands out of sync; you can’t run this command now” errors from the MySQLdb library.
I didn’t have time to debug it right away and the searches revealed only various threads where users complained about similar problems without any obvious solution. So at the time I just wrote an exception handler around the database functions that will retry to execute the query when such an error is encountered, this proved to be an rather effective bandage since there were only about 2% of the cases when we got double fault.
Anyway, the problem kept ticking in the back of my mind for some weeks until I accidentally read a post by John Nagel explaining that the cursors abstraction provided by the Pythons MySQLdb library is just an illusion since the underlying MySQL C library doesn’t really support cursors and requires you to always free the results for the last query before executing next one on the same connection. Our database connection spooling library was written with an assumption that there can be multiple open cursors on the same connection and the connections were released back to the spool as soon as the execute() returned and after that the cursor object was returned back to the caller.
This lead to the following race condition which produced the above mentioned “commands out of sync” errors:
- thread 1 executes a query on connection 1 which returns results
- connection 1 is returned to the spool and cursor object is returned to the caller in the thread 1
- thread 2 gets the connection 1 from the spool & tries to execute a query on it (before the thread 1 has called fetchone()/fetchall() on the cursor that it has)
So I guess the morale of the story is that I shouldn’t make intuitive assumptions and should read the actual documentation more. Eeasier said than done.