Asyncpg with sqlalchemy cannot perform operations at the same time

See original GitHub issue

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

Issue Analytics

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

github_iconTop GitHub Comments

1reaction
elpranscommented, Mar 8, 2021

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.

0reactions
CaselITcommented, Mar 9, 2021

Ok, thanks!

Read more comments on GitHub >

github_iconTop Results From Across the Web

python - SQLAlchemy with asyncpg crashing with error ...
SQLAlchemy with asyncpg crashing with error: asyncpg.InterfaceError - cannot perform operation: another operation is in progress · Ask Question.
Read more >
Asynchronous I/O (asyncio) - SQLAlchemy 1.4 Documentation
The program can freely switch between async/await code and contained functions that use sync code with virtually no performance penalty.
Read more >
r/Python - SQLAlchemy with asyncpg cannot perform operation
You seem to be running out of connections which is quite rare so it's possible there are multiple async calls bring made on...
Read more >
Connect to PostgreSQL with SQLAlchemy and asyncio - Makimo
This tutorial shows how to connect to the PostgreSQL database within an asynchronous environment using SQLAlchemy and asyncio.
Read more >
gino · PyPI
GINO Is Not ORM - a Python ORM on asyncpg and SQLAlchemy core. ... fantix # Update affects only database row and the...
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found