Python mysqlclient Doesn't Work Well with gevent

mysqlclient (MySQLdb fork) is a Python MySQL driver which uses libmysqlclient (or libmariadbclient). This means speed and stability. Does it work well with gevent? Not really.

In mysqlclient <= v1.3.13, there’s a sample called waiter_gevent.py which looks like this:

from __future__ import print_function
"""Demo using Gevent with mysqlclient."""

import gevent.hub
import MySQLdb


def gevent_waiter(fd, hub=gevent.hub.get_hub()):
    hub.wait(hub.loop.io(fd, 1))


def f(n):
    conn = MySQLdb.connect(user='root', waiter=gevent_waiter)
    cur = conn.cursor()
    cur.execute("SELECT SLEEP(%s)", (n,))
    cur.execute("SELECT 1+%s", (n,))
    print(cur.fetchall()[0])


gevent.spawn(f, 1)
gevent.spawn(f, 2)
gevent.spawn(f, 3)
gevent.spawn(f, 4)
gevent.sleep(5)

This allows gevent to yield to other greenlets when mysqlclient is waiting for the response of a query. This is clever, but it comes with its quirks.

In connections.py, the waiter is only used for query(). Connection establishment, commit and rollback do not use query() but they call functions in _mysql.c which in turn call the library functions (e.g. mysql_commit() and mysql_rollback()). Those are blocking IO calls designed for the threading paradigm.

gevent programs are single-threaded. If there are non-cooperative blocking calls, it blocks the whole program. If there is a slow commit, it will block the program for the entire period.

To workaround blocking commit and rollback, instead of calling the C mysql_commit() and mysql_rollback(), we can call the cooperative query() function in Python.

Side note: mysql_commit() and mysql_rollback don’t do any magic, they are just wrappers of mysql_real_query(). Feel free to look at the MySQL source:

bool STDCALL mysql_commit(MYSQL *mysql) {
  DBUG_ENTER("mysql_commit");
  DBUG_RETURN((bool)mysql_real_query(mysql, "commit", 6));
}

bool STDCALL mysql_rollback(MYSQL *mysql) {
  DBUG_ENTER("mysql_rollback");
  DBUG_RETURN((bool)mysql_real_query(mysql, "rollback", 8));
}

Therefore, the workaround would look like this in mysqlclient connections.py:

class Connection(_mysql.connection):
    def commit(self):
        self.query("COMMIT")

    def rollback(self):
        self.query("ROLLBACK")

For connection establishment, there is no such simple workaround.

In conclusion, if you don’t want unpleasent surprises and performance is not a problem, use PyMySQL. In fact, the waiter parameter is deprecated in mysqlclient v1.3.14 and remove in v1.4.0+, which is reasonable because mysqlclient is not designed to work with gevent anyway.