Practice Now
Python
async
aioodbc
blocking
Python 3.6

Python 3.6 async aioodbc blocking

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions
ConcurrencyPython

Introduction

aioodbc is an async wrapper around pyodbc that uses a thread pool executor to run blocking ODBC calls without stalling the event loop. Despite being async, aioodbc can still block when the underlying ODBC driver performs synchronous operations, when connections are not properly awaited, or when the thread pool is exhausted. The fix involves correctly awaiting all database calls, configuring the executor pool size, and understanding that aioodbc provides concurrency (not true parallelism) for database I/O.

How aioodbc Works

aioodbc wraps the synchronous pyodbc library by running each blocking call in a concurrent.futures.ThreadPoolExecutor:

python
1import asyncio
2import aioodbc
3
4async def main():
5    dsn = 'Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=mydb;UID=user;PWD=pass'
6
7    async with aioodbc.create_pool(dsn=dsn, minsize=1, maxsize=10) as pool:
8        async with pool.acquire() as conn:
9            async with conn.cursor() as cursor:
10                await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
11                row = await cursor.fetchone()
12                print(row)
13
14asyncio.run(main())

Under the hood, await cursor.execute(...) calls pyodbc.Cursor.execute(...) inside a thread. This means the event loop is free to handle other coroutines while the database query runs.

Why aioodbc Blocks

Missing await Keywords

Forgetting await on async calls returns a coroutine object instead of executing the query:

python
1# WRONG — missing await, returns coroutine, may block or silently fail
2async def get_user(pool, user_id):
3    async with pool.acquire() as conn:
4        async with conn.cursor() as cursor:
5            cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))  # Missing await!
6            row = cursor.fetchone()  # Missing await!
7            return row
8
9# CORRECT — await all async operations
10async def get_user(pool, user_id):
11    async with pool.acquire() as conn:
12        async with conn.cursor() as cursor:
13            await cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
14            row = await cursor.fetchone()
15            return row

Thread Pool Exhaustion

The default executor has a limited number of threads. If all threads are occupied by long-running queries, new database calls block waiting for a thread:

python
1import asyncio
2import aioodbc
3
4async def slow_query(pool, query_id):
5    async with pool.acquire() as conn:
6        async with conn.cursor() as cursor:
7            # This query takes 30 seconds
8            await cursor.execute("WAITFOR DELAY '00:00:30'; SELECT 1")
9            print(f"Query {query_id} done")
10
11async def main():
12    dsn = 'Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=mydb;'
13
14    # Pool with only 2 connections — third query blocks
15    async with aioodbc.create_pool(dsn=dsn, minsize=1, maxsize=2) as pool:
16        tasks = [slow_query(pool, i) for i in range(5)]
17        await asyncio.gather(*tasks)  # Only 2 run concurrently
18
19asyncio.run(main())

Fix by increasing the pool size:

python
1# Increase connection pool to match concurrency needs
2async with aioodbc.create_pool(dsn=dsn, minsize=5, maxsize=20) as pool:
3    tasks = [slow_query(pool, i) for i in range(20)]
4    await asyncio.gather(*tasks)

Synchronous Code in Async Context

Mixing synchronous pyodbc calls with async code blocks the event loop:

python
1import pyodbc
2
3# WRONG — blocks the entire event loop
4async def bad_query():
5    conn = pyodbc.connect(dsn)  # Synchronous! Blocks event loop
6    cursor = conn.cursor()
7    cursor.execute("SELECT * FROM large_table")  # Blocks event loop
8    return cursor.fetchall()
9
10# CORRECT — use aioodbc or run_in_executor
11async def good_query_with_executor(dsn):
12    loop = asyncio.get_event_loop()
13    conn = await loop.run_in_executor(None, pyodbc.connect, dsn)
14    cursor = conn.cursor()
15    rows = await loop.run_in_executor(None, cursor.execute, "SELECT * FROM large_table")
16    return await loop.run_in_executor(None, cursor.fetchall)

Connection Pool Configuration

python
1async def create_optimized_pool():
2    pool = await aioodbc.create_pool(
3        dsn='Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=mydb;',
4        minsize=5,       # Minimum connections kept open
5        maxsize=20,      # Maximum connections allowed
6        pool_recycle=3600,  # Recycle connections after 1 hour
7        echo=True        # Log SQL statements for debugging
8    )
9    return pool

Debugging Blocking Issues

Use asyncio debug mode to detect coroutines that block the event loop:

python
1import asyncio
2import logging
3
4# Enable asyncio debug mode
5logging.basicConfig(level=logging.DEBUG)
6loop = asyncio.new_event_loop()
7loop.set_debug(True)
8loop.slow_callback_duration = 0.1  # Warn if callback takes > 100ms
9
10async def main():
11    async with aioodbc.create_pool(dsn=dsn) as pool:
12        async with pool.acquire() as conn:
13            async with conn.cursor() as cursor:
14                await cursor.execute("SELECT * FROM users")
15                rows = await cursor.fetchall()
16                return rows
17
18loop.run_until_complete(main())

When debug mode is on, Python logs warnings like Executing <Task> took 0.5 seconds for any blocking call.

Alternative Async Database Libraries

If aioodbc blocking is a persistent problem, consider native async alternatives:

python
1# For PostgreSQL — use asyncpg (native async, no thread pool)
2import asyncpg
3
4async def query_postgres():
5    conn = await asyncpg.connect('postgresql://user:pass@localhost/mydb')
6    rows = await conn.fetch("SELECT * FROM users WHERE id = $1", 1)
7    await conn.close()
8    return rows
9
10# For MySQL — use aiomysql
11import aiomysql
12
13async def query_mysql():
14    conn = await aiomysql.connect(host='localhost', user='root', db='mydb')
15    async with conn.cursor() as cursor:
16        await cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
17        row = await cursor.fetchone()
18    conn.close()
19    return row

Native async drivers like asyncpg use non-blocking I/O directly instead of wrapping synchronous calls in threads, providing better performance and true non-blocking behavior.

Common Pitfalls

  • Forgetting await on cursor methods: cursor.execute() without await returns a coroutine and never actually runs the query. Always await all aioodbc cursor and connection methods.
  • Connection pool too small for concurrent load: If maxsize=5 but 20 coroutines need connections simultaneously, 15 coroutines block waiting. Size the pool to match peak concurrency.
  • Mixing synchronous pyodbc with async code: Calling pyodbc.connect() directly in an async function blocks the event loop. Use aioodbc or wrap calls in loop.run_in_executor().
  • Not closing connections or pools: Leaked connections exhaust the pool. Always use async with context managers to ensure cleanup.
  • Assuming aioodbc is truly non-blocking: aioodbc uses threads to avoid blocking the event loop, but the underlying ODBC driver calls are still synchronous. Long queries tie up a thread for their entire duration.

Summary

  • aioodbc wraps synchronous pyodbc in a thread pool executor to provide async database access
  • Always await every aioodbc method call (execute, fetchone, fetchall, commit)
  • Size the connection pool (maxsize) to match your peak concurrent query load
  • Enable asyncio debug mode (loop.set_debug(True)) to detect blocking calls
  • For better async performance, consider native async drivers like asyncpg (PostgreSQL) or aiomysql (MySQL)
Related reading
Free course
Beginner
7 lessons
2 hours
Tackling System Design Interview Problems

A short course that equips you with the skills to approach system design interviews methodically.

Start the free course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.