Skip to content

Using MySQL from asyncio

Python has two maintained asyncio MySQL drivers, aiomysql and asyncmy, and both default to autocommit=False — which, combined with MySQL's REPEATABLE READ default isolation and their pool's handling of open transactions, produces two different and surprising failures. Measured on Python 3.14 against MySQL 8.4 with aiomysql 0.3.2 and asyncmy 0.2.15: with default settings, an aiomysql pool connection kept returning a balance of 1.50 after another connection had committed 999, because the pooled connection's read snapshot from an earlier query was still open. asyncmy returned the new value — because its pool discarded every connection that had run a query, opening 999 new server connections in 1,000 queries and taking 1,114 µs per query instead of 192 µs. With autocommit=True, both read fresh data on reused connections. On throughput, asyncmy reached 14,832 point queries per second at 61 µs of client CPU each, aiomysql 9,658 at 95 µs, and 20 PyMySQL threads 11,518 at 121 µs. And a query abandoned by asyncio.timeout kept running on the server until a MAX_EXECUTION_TIME hint stopped it at 0.20 s. This guide sets up either driver to avoid all three.

Prerequisites

1. Turn on autocommit and use explicit transactions

With autocommit=False, even a SELECT starts a transaction, and under REPEATABLE READ that transaction reads from a snapshot taken at its first read. What happens next depends on the pool:

async def read_balance(pool):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT balance FROM acct WHERE id = 1")
            return (await cur.fetchone())[0]

Measured with a one-connection pool, reading the row, committing an update to 999 from a separate connection, then reading again: aiomysql with defaults returned 1.50 both times. Its pool returned the connection to the free list with the snapshot still open, so the second read saw the old data. asyncmy with defaults returned 999 — but only because its pool closed any connection with an open transaction on release: 1,000 reads opened 999 new server connections and averaged 1,114 µs, against 192 µs with autocommit=True. Set autocommit=True on the pool, and wrap multi-statement work in an explicit transaction:

pool = await asyncmy.create_pool(host=..., user=..., password=..., db="app",
                                 autocommit=True, minsize=5, maxsize=20)

async with pool.acquire() as conn:
    await conn.begin()
    try:
        async with conn.cursor() as cur:
            await cur.execute("UPDATE acct SET balance = balance - %s WHERE id = %s", (amt, src))
            await cur.execute("UPDATE acct SET balance = balance + %s WHERE id = %s", (amt, dst))
        await conn.commit()
    except BaseException:
        await conn.rollback()
        raise

Verify: a read after another connection commits returns the new value, and the server's Connections status counter does not grow during a run of pooled queries.

Default autocommit=False with a pool, MySQL 8.4 REPEATABLE READ A grid of 4 rows by 4 columns. Default autocommit=False with a pool, MySQL 8.4 REPEATABLE READ driver and setting read after commit of 999 new connections per 1,000 queries time per query aiomysql, defaults 1.50 (stale) 0 174 us asyncmy, defaults 999.00 999 1,114 us aiomysql, autocommit=True 999.00 0 189 us asyncmy, autocommit=True 999.00 0 192 us aiomysql 0.3.2, asyncmy 0.2.15; one-connection pools.

2. Compare drivers on throughput and CPU

With autocommit set, measure the drivers on the service's own query shape. Here: 200 tasks running primary-key lookups through a 20-connection pool, with a semaphore of 20 in front, for 4 seconds:

async def worker():
    r = random.Random()
    while time.monotonic() < stop:
        async with slots, pool.acquire() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT owner, balance FROM acct WHERE id = %s",
                                  (r.randint(1, 100_000),))
                await cur.fetchone()

Measured: asyncmy, whose protocol code is compiled with Cython, reached 14,832 queries per second at 61 µs of client CPU each; aiomysql, pure Python on top of PyMySQL's protocol code, 9,658 at 95 µs; and 20 threads using synchronous PyMySQL, each with its own connection, 11,518 at 121 µs. At these rates the client process was the bottleneck, so CPU per query decided the result. For services where MySQL itself is the limit, the drivers will look alike, as with PostgreSQL in asyncio vs threads for database-heavy services.

Verify: the driver choice is based on a benchmark of the service's own queries, with client CPU recorded.

3. Keep large results from stalling the loop

Result rows are decoded in the client, on the event loop. Measured with a probe task timing 1 ms sleeps while fetching all 100,000 rows of the table:

async with conn.cursor(asyncmy.cursors.SSCursor) as cur:     # unbuffered, server-side
    await cur.execute("SELECT id, owner, balance FROM acct")
    while batch := await cur.fetchmany(1000):
        await handle(batch)

asyncmy's fetchall() took 0.08 s with a longest loop stall of 14 ms; its unbuffered SSCursor with fetchmany(1000) took 0.05 s with a longest stall of 15.8 ms. aiomysql's fetchall() took 0.34 s and stalled the loop for up to 73 ms; its SSCursor took 0.35 s with a 56.6 ms stall. The unbuffered cursor's main benefit is memory — rows are not all held at once — and while it is open, the connection cannot run another query. For large exports through aiomysql, run them on a separate worker or process, so that 50–70 ms stalls do not reach request handlers.

Verify: for the largest result the service fetches, a probe shows how long the loop stalls, and the figure is within the latency budget.

Client CPU per point query, 20 connections 3 horizontal bars comparing PyMySQL, 20 threads with the others. Client CPU per point query, 20 connections PyMySQL, 20 threads 121 us aiomysql 95 us asyncmy 61 us Throughput: 11,518, 9,658 and 14,832 queries/s. Primary-key lookups, 200 concurrent tasks, 4-second runs.

4. Bound query time on the server as well

A client-side timeout stops waiting; it does not stop MySQL. Measured with asyncio.timeout(0.2) around SELECT SLEEP(2):

async with asyncio.timeout(0.2):
    async with pool.acquire() as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT SLEEP(2)")

Both drivers raised TimeoutError after 0.20 s, closed the connection rather than returning it to the pool in an unknown state, and the next query on the pool succeeded in 2 ms. But the processlist still showed the SLEEP running on the server. A slow query abandoned this way keeps its locks and CPU until it finishes. MySQL's optimizer hint bounds read-only SELECT statements on the server:

await cur.execute(
    "SELECT /*+ MAX_EXECUTION_TIME(200) */ COUNT(*) FROM acct a JOIN acct b ON a.owner < b.owner"
)

Measured: the join was stopped after 0.20 s with error 3024, "maximum statement execution time exceeded". Applied to SLEEP(2), the hint made it return after 0.20 s. MAX_EXECUTION_TIME applies only to SELECT; for writes, keep transactions short and set innodb_lock_wait_timeout per session. Use the client timeout as a slightly longer backstop, as described in timing out database queries.

Verify: after a client-side timeout, the processlist shows no surviving query from that request.

5. Choose and configure a driver

Both drivers have the same shape — a pool, cursors, %s placeholders — so switching is mostly a matter of import names. A configuration that applies the measurements:

pool = await asyncmy.create_pool(
    host="db", port=3306, user="app", password=secret, db="app",
    autocommit=True,              # fresh reads, no reconnect per query
    minsize=5, maxsize=20,        # below max_connections divided by processes
    pool_recycle=3600,            # replace connections before server wait_timeout
)
slots = asyncio.Semaphore(20)     # FIFO waiting in front of the pool

asyncmy had the lower CPU cost and shorter loop stalls; aiomysql has the longer history and SQLAlchemy integration through mysql+aiomysql, and SQLAlchemy also supports mysql+asyncmy. With either, MySQL's max_connections — 151 by default here — is shared by every process, so a pool of 20 per worker allows at most seven workers.

Verify: autocommit=True is set, the sum of all pools is below max_connections, and slow SELECTs carry a server-side time limit.

A safe query path to MySQL A flow of 5 stages. A safe query path to MySQL Semaphore FIFO, sized to the pool Acquire autocommit=True connection Execute MAX_EXECUTION_TIME hint + client timeout Fetch batches for large results Release connection back to the pool Explicit begin/commit only where several statements must be atomic.

Verification

MySQL is used safely from asyncio when:

  • autocommit=True is set, so pooled connections neither serve stale snapshots nor reconnect per query.
  • Multi-statement writes use explicit transactions with rollback on any exception.
  • Long SELECTs carry MAX_EXECUTION_TIME, and abandoned queries do not linger in the processlist.
  • Loop stalls from large results are measured and within budget.

Diagnostic Hook: when a pooled MySQL service reads data that another service has already changed, check the driver's autocommit setting. A connection returned to the pool with an open REPEATABLE READ transaction keeps serving its old snapshot — 1.50 after 999 was committed, in this test.

Pitfalls & edge cases

  • Default autocommit=False with aiomysql. Measured: stale reads from pooled connections.
  • Default autocommit=False with asyncmy. Measured: 999 reconnects per 1,000 queries.
  • Client timeouts alone. The server kept running the abandoned query.
  • Pools that add up past max_connections. The default is 151, shared by all processes.

Frequently Asked Questions

Should I use aiomysql or asyncmy?

asyncmy was faster here, 14,832 against 9,658 queries per second at 61 against 95 µs of CPU. Both need autocommit=True with their pools.

Why does aiomysql return stale data?

With autocommit off, a SELECT opens a REPEATABLE READ snapshot that stays open when the connection returns to the pool. A later read saw 1.50 after 999 was committed.

Why is asyncmy opening a new connection for every query?

With autocommit off, every query leaves a transaction open, and the pool closes such connections on release: 999 new connections in 1,000 queries. Set autocommit=True.

Does asyncio.timeout cancel a MySQL query on the server?

No. The client stopped after 0.20 s but the query kept running. A MAX_EXECUTION_TIME hint stopped a SELECT on the server at 0.20 s.