UniqueViolationError within a transaction (using get_or_create)

[powellnorma]

I noticed that when multiple concurrent executions of my add_list coroutine overlap, occasionally I get UniqueViolationError. I initially thought this was protected against by using a transaction. But it seems that the transaction does not block other coroutines from starting another transaction (on same table) as well, is that correct?

For illustration, the add_list function looks something like the following, and is started multiple times (concurrently), with almost no break in between.

async def add_main(i):
    m = await Main.objects().get_or_create(Main.key == f"Main-{i}")
    a = await EntityA.objects().get_or_create(EntityA.key == f"EntityA-{i}")
    m.foreign_1 = a
    await m.save()

async def add_list():
    async with Main._meta.db.transaction():
        for i in range(100):
            await add_main(i)

I don't get the UniqueViolationError on the "Main" table, but on a related one (e.g. EntityA), and while doing get_or_create. I think I will add an with asyncio.Lock before the transaction - Or are there better ways to prevent the UniqueViolationError from occurring?

[dantownsend]

But it seems that the transaction does not block other coroutines from starting another transaction (on same table) as well, is that correct?

That's right - you can potentially have several coroutines executing concurrently, each one with its own transaction.

In terms of the unique violation error, I can only assume that the following is happening:

• get_or_create runs in one transaction - thinks the row doesn't exist so tries to create it
• meanwhile, get_or_create is running in another transaction, and tries to the do the same
• the first transaction resolves, and creates the row
• the second transaction resolves, the row already exists, so we get an error

In terms of avoiding it - maybe a lock would be OK. It's a tricky one. Maybe try creating the transaction at a higher level, so they all share the same transaction:

async def add_list():
    for i in range(100):
        await add_main(i)

# Move the transaction up, into whatever is calling `add_list`.
async def main():
    async with Main._meta.db.transaction():
        await add_list()

[powellnorma]

Thank you again for your explanation.

the second transaction resolves, the row already exists, so we get an error

Ok, I would expect that since the transaction is resolved at the end of the block, that we don't see the original create_or_replace in the traceback anymore. But this seems to be the case for me, how is that possible?

you can potentially have several coroutines executing concurrently, each one with its own transaction.

Ok, so one could say, that (in this case) the "isolation" (as in ACID) is compromised a bit for the sake of performance gains? And if I wanted to cover edge cases very well, I would have to catch potential UniqueViolationError on every get_or_create, even if not in an transaction, correct?

Maybe try creating the transaction at a higher level, so they all share the same transaction

This sounds good, but in my case the transactions are caused by requests to the app, so I don't think I can apply this. I currently use an asyncio.Lock, that seems to work fine for now