tortoise-orm: Parallel execution of multiple `get_or_create` calls wrapped with transaction raises `TransactionManagementError` with postgresql backend

Describe the bug This bug appears when multiple conditions are met:

  1. DB backend is postgresql
  2. Function containing get_or_create call is wrapped with in_transaction
  3. This function is invoked multiple times in parallel using asyncio.gather

Seems like some sort of race condition.

To Reproduce

test_models.py

from tortoise.models import Model
import tortoise.fields as fields

class TestModel(Model):
    id = fields.IntField(pk=True)

test.py

import asyncio
import logging
from test_models import TestModel
from tortoise import Tortoise
from tortoise.transactions import in_transaction

logging.basicConfig()
logging.getLogger().setLevel(0)


async def test():
    async with in_transaction():
        await TestModel.get_or_create(id=1)
        await TestModel.get_or_create(id=2)


async def main():
    await Tortoise.init(
        db_url='postgres://postgres@127.0.0.1:13337',
        # db_url='sqlite://:memory:',
        modules={'test': ['test_models']},
    )
    async with in_transaction() as conn:
        await conn.execute_query('DROP TABLE IF EXISTS testmodel')
    await Tortoise.generate_schemas()

    await asyncio.gather(
        test(),
        test(),
        test(),
    ),

asyncio.run(main())

log

(.venv) ➜  dipdup-dex git:(feat/initial) ✗ python test.py
DEBUG:asyncio:Using selector: EpollSelector
DEBUG:tortoise:Tortoise-ORM startup
    connections: {'default': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'port': 13337, 'database': None, 'host': '127.0.0.1', 'user': 'postgres', 'password': None}}}
    apps: {'test': {'models': ['test_models'], 'default_connection': 'default'}}
DEBUG:db_client:Created connection pool <asyncpg.pool.Pool object at 0x7fd6be274110> with params: {'host': '127.0.0.1', 'port': 13337, 'user': 'postgres', 'database': None, 'min_size': 1, 'max_size': 5, 'connection_class': <class 'asyncpg.connection.Connection'>, 'loop': None}
DEBUG:db_client:DROP TABLE IF EXISTS testmodel: None
DEBUG:tortoise:Creating schema: CREATE TABLE IF NOT EXISTS "testmodel" (
    "id" SERIAL NOT NULL PRIMARY KEY
);
DEBUG:db_client:CREATE TABLE IF NOT EXISTS "testmodel" (
    "id" SERIAL NOT NULL PRIMARY KEY
);
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=1 LIMIT 2: None
DEBUG:db_client:INSERT INTO "testmodel" ("id") VALUES ($1): [1]
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=1 LIMIT 2: None
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=1 LIMIT 2: None
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=2 LIMIT 2: None
DEBUG:db_client:INSERT INTO "testmodel" ("id") VALUES ($1): [1]
DEBUG:db_client:INSERT INTO "testmodel" ("id") VALUES ($1): [2]
DEBUG:db_client:INSERT INTO "testmodel" ("id") VALUES ($1): [1]
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=1 LIMIT 2: None
DEBUG:db_client:SELECT "id" FROM "testmodel" WHERE "id"=1 LIMIT 2: None
ERROR:asyncio:Resetting connection with an active transaction <asyncpg.connection.Connection object at 0x7fd6be11e9e0>
ERROR:asyncio:Resetting connection with an active transaction <asyncpg.connection.Connection object at 0x7fd6be11eac0>
Traceback (most recent call last):
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/models.py", line 1041, in get_or_create
    return await cls.filter(**kwargs).using_db(connection).get(), False
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/queryset.py", line 891, in _execute
    raise DoesNotExist("Object does not exist")
tortoise.exceptions.DoesNotExist: Object does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/models.py", line 1044, in get_or_create
    return await cls.create(using_db=connection, **defaults, **kwargs), True
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 40, in translate_exceptions_
    raise IntegrityError(exc)
tortoise.exceptions.IntegrityError: duplicate key value violates unique constraint "testmodel_pkey"
DETAIL:  Key (id)=(1) already exists.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/droserasprout/git/dipdup-dex/test.py", line 33, in <module>
    asyncio.run(main())
  File "/usr/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/home/droserasprout/git/dipdup-dex/test.py", line 27, in main
    await asyncio.gather(
  File "/home/droserasprout/git/dipdup-dex/test.py", line 13, in test
    await TestModel.get_or_create(id=1)
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/models.py", line 1046, in get_or_create
    return await cls.filter(**kwargs).using_db(connection).get(), False
  File "/home/droserasprout/git/dipdup-dex/.venv/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 42, in translate_exceptions_
    raise TransactionManagementError(exc)
tortoise.exceptions.TransactionManagementError: current transaction is aborted, commands ignored until end of transaction block

Expected behavior All of 6 get_or_create calls executed correctly.

Additional context Reproducible since 0.17.1 until 0.17.4. Using latest postgres Docker image. Possibly related: https://github.com/tortoise/tortoise-orm/issues/744

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Reactions: 2
  • Comments: 18 (14 by maintainers)

Most upvoted comments

Without in_transaction, the issue can still be reproduced easily:

import asyncio
import logging
from test_models import TestModel
from tortoise import Tortoise
from tortoise.transactions import in_transaction

logging.basicConfig()
logging.getLogger().setLevel(0)


async def test():
-    async with in_transaction():
-        await TestModel.get_or_create(id=1)
-        await TestModel.get_or_create(id=2)
+    await TestModel.get_or_create(id=1)
+    await TestModel.get_or_create(id=2)


async def main():
    await Tortoise.init(
        db_url='postgres://postgres@127.0.0.1:13337',
        # db_url='sqlite://:memory:',
        modules={'test': ['test_models']},
    )
    async with in_transaction() as conn:
        await conn.execute_query('DROP TABLE IF EXISTS testmodel')
    await Tortoise.generate_schemas()

-    await asyncio.gather(
-        test(),
-        test(),
-        test(),
-    ),
+    await asyncio.gather(*(test() for _ in range(16)))

asyncio.run(main())

@long2ice Any news on this issue?

I am facing exactly the same* issue even nothing is wrapped with in_transaction.

*:

Traceback (most recent call last):
  File "/opt/venv/lib/python3.10/site-packages/tortoise/models.py", line 1056, in get_or_create
    await cls.select_for_update().filter(**kwargs).using_db(connection).get(),
  File "/opt/venv/lib/python3.10/site-packages/tortoise/queryset.py", line 1018, in _execute
    raise DoesNotExist("Object does not exist")
tortoise.exceptions.DoesNotExist: Object does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/venv/lib/python3.10/site-packages/tortoise/models.py", line 1061, in get_or_create
    return await cls.create(using_db=connection, **defaults, **kwargs), True
  File "/opt/venv/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 83, in _translate_exceptions
    raise IntegrityError(exc)
tortoise.exceptions.IntegrityError: duplicate key value violates unique constraint "uid_sub_user_id_029239"
DETAIL:  Key (user_id, feed_id)=(<REDACTED>, <REDACTED>) already exists.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/app/src/command/inner/sub.py", line 93, in sub
    _sub, created_new_sub = await db.Sub.get_or_create(user_id=user_id, feed=feed,
  File "/opt/venv/lib/python3.10/site-packages/tortoise/models.py", line 1063, in get_or_create
    return await cls.filter(**kwargs).using_db(connection).get(), False
  File "/opt/venv/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 85, in _translate_exceptions
    raise TransactionManagementError(exc)
tortoise.exceptions.TransactionManagementError: current transaction is aborted, commands ignored until end of transaction block