sqlalchemy: Asyncpg with sqlalchemy cannot perform operations at the same time

Describe the bug I’m using the new async engine with asyncpg (postgresql), for a discord bot, that is running with asyncio. I tried to access the database at the same time with 2 same event handlers in different files. This caused asyncpg to raise asyncpg.exceptions._base.InterfaceError, which then lead to sqlalchemy.exc.InterfaceError. I’m using ORM structure and I tried to perform a read operation like this.

Expected behavior SQLAlchemy should be able to handle running 2 operations at the same time, considering it aims to be asynchronous. Even though it may not happen often, there are cases when the first operation is still running and another request has already been made. Perhaps that operation should wait (asynchronously), and only execute once the first operation is finished, to prevent this error.

To Reproduce If you want a complete reproduction, you can checkout this commit in my bot repository: https://github.com/Codin-Nerds/Neutron-Bot/commit/3aeabbed04bec342d970497bd5751f940482f196 and run the bot (with PostgreSQL hosted, or in docker, follow README for more details).

But simplified: when 2 operations are made asynchronously at the same time, this error occurs

Error

Ignoring exception in on_member_update
Traceback (most recent call last):
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 530, in _start_transaction
    await self._transaction.start()
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/asyncpg/transaction.py", line 138, in start
    await self._connection.execute(query)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/asyncpg/connection.py", line 295, in execute
    return await self._protocol.query(query, timeout)
  File "asyncpg/protocol/protocol.pyx", line 301, in query
  File "asyncpg/protocol/protocol.pyx", line 664, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1628, in _execute_context
    self.dialect.do_execute(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 663, in do_execute
    cursor.execute(statement, parameters)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 350, in execute
    self._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 290, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 513, in _handle_exception
    raise error
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 346, in execute
    self._adapt_connection.await_(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 41, in await_only
    return current.driver.switch(awaitable)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 86, in greenlet_spawn
    value = await result
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 312, in _prepare_and_execute
    await self._adapt_connection._start_transaction()
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 532, in _start_transaction
    self._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 509, in _handle_exception
    raise translated_error from error
sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_dbapi.InterfaceError: <class 'asyncpg.exceptions._base.InterfaceError'>: cannot perform operation: another operation is in progress

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/bot/cogs/logging/member_log.py", line 84, in on_member_update
    member_log_id = await LogChannels.get_log_channel(self.bot.db_session, "member_log", member_after.guild)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/bot/database/log_channels.py", line 76, in get_log_channel
    log_channels = await cls.get_log_channels(session, guild)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/bot/database/log_channels.py", line 64, in get_log_channels
    row = await session.run_sync(lambda session: session.query(cls).filter_by(guild=guild).one())
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/ext/asyncio/session.py", line 120, in run_sync
    return await greenlet_spawn(fn, self.sync_session, *arg, **kw)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 91, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/bot/database/log_channels.py", line 64, in <lambda>
    row = await session.run_sync(lambda session: session.query(cls).filter_by(guild=guild).one())
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2695, in one
    return self._iter().one()
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2733, in _iter
    result = self.session.execute(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1530, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1443, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 310, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1312, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1671, in _execute_context
    self._handle_dbapi_exception(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1852, in _handle_dbapi_exception
    util.raise_(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 180, in raise_
    raise exception
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1628, in _execute_context
    self.dialect.do_execute(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 663, in do_execute
    cursor.execute(statement, parameters)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 350, in execute
    self._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 290, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 513, in _handle_exception
    raise error
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 346, in execute
    self._adapt_connection.await_(
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 41, in await_only
    return current.driver.switch(awaitable)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 86, in greenlet_spawn
    value = await result
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 312, in _prepare_and_execute
    await self._adapt_connection._start_transaction()
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 532, in _start_transaction
    self._handle_exception(error)
  File "/home/itsdrike/Programming/Python/GitHub/Codin-Nerds/Neutron-Bot/.venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 509, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.InterfaceError: (sqlalchemy.dialects.postgresql.asyncpg.InterfaceError) <class 'asyncpg.exceptions._base.InterfaceError'>: cannot perform operation: another operation is in progress
[SQL: SELECT log_channels.guild AS log_channels_guild, log_channels.server_log AS log_channels_server_log, log_channels.mod_log AS log_channels_mod_log, log_channels.message_log AS log_channels_message_log, log_channels.member_log AS log_channels_member_log, log_channels.join_log AS log_channels_join_log, log_channels.voice_log AS log_channels_voice_log 
FROM log_channels 
WHERE log_channels.guild = ?]
[parameters: ('608625191068631040',)]
(Background on this error at: http://sqlalche.me/e/14/rvf5)

Versions.

OS: Arch Linux (linux 5.10.16.arch1-1)
Python: 3.8
SQLAlchemy: 1.4.0b1
Database: PostgreSQL 13.2-1
DBAPI: I’m not familiar with what this is

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 33 (24 by maintainers)

Commits related to this issue

Temporarely disable mute logs Mute logs currently can't work, due to issues with reading from the same database at the same time. This is currently being resolved in SQLAlchemy issue: https://github.c... — committed to Codin-Nerds/Neutron-Bot by ItsDrike 3 years ago
Make new database session on every operation - Working with a single session isn't thread-safe and causes issues if these operations would happen concurrently - AsyncIO doesn't run completely concur... — committed to Codin-Nerds/Neutron-Bot by ItsDrike 3 years ago
Temporarely disable mute logs Mute logs currently can't work, due to issues with reading from the same database at the same time. This is currently being resolved in SQLAlchemy issue: https://github.c... — committed to Codin-Nerds/Neutron-Bot by ItsDrike 3 years ago
Make new database session on every operation - Working with a single session isn't thread-safe and causes issues if these operations would happen concurrently - AsyncIO doesn't run completely concur... — committed to Codin-Nerds/Neutron-Bot by ItsDrike 3 years ago

Most upvoted comments

it requires that you check out connections explicitly with aquire(). same as engine.connect().

FWIW, Pool exposes the fetch*() and execute() methods directly. IOW, you can treat an asyncpg.Pool instance as a concurrency-safe “connection” that you can pass to your asyncio.gather, and this is what we recommend people do in application code. The real reason why asyncpg.Pool isn’t useful for SQLAlchemy is because you’re relying on explicit prepared statements, and those are connection-bound. Transactions too.

elprans on Mar 8, 2021