MySQL row locking problem with bulk_create

[sharp girder]

I'm getting an error that the database is failing to get a lock on a table while I'm trying to do a bulk_create():
django.db.utils.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

Since it's a bulk_create() I don't think there's a way to use select_for_update() to get my own lock ahead of time. Honestly since it's on a create, I'm not sure what kind of lock might be involved in any case, since it's doing an insert. Any thoughts on how to resolve this, or where to investigate it more?

[lofty mural]

oh yeah, you did say something about MySQL sometimes deciding to lock the whole table.

I haven't seriously used MySQL in a long time, but I remember it always spitting out utterly mysterious errors like that. Which is why I stopped using it 😐

[sharp girder]

Sadly I don't have the option of not using it. 😦

[onyx patio]

Ordering is a key factor in causing deadlocks. If you're doing a lot of inserts, make sure to insert in a consistent order. The docs for MySQL will probably help you figure out exactly what's going on https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks-handling.html

I also found the Postgres docs on locking helpful to understand a concrete example of a deadlock https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS

[sharp girder]

In this case it was caused by a collision in the DB between different workers, so I don't have any control over the order of the operations. Reading that doc you sent tho, I was struck by "Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock." which is obvious, but I didn't think about. I think I'm reaching for a more complicated solution where a simpler one is better. I'm just going to wrap my bulk_create() in a try/except and monitor to see how often it comes up.

def bulk_set_file_permissions(paths: list, uwnetid: str = None):
    """Set the permission for a UWNetID for a list of paths"""

    if not uwnetid:
        request = get_request_object()
        if hasattr(request, "uw_net_id"):
            uwnetid = get_request_object().uw_net_id
        elif hasattr(request, "net_id"):
            uwnetid = get_request_object().net_id

    create_list = [UWNetIDAuth(uwnetid=uwnetid, path=path) for path in paths]

    try:
        UWNetIDAuth.objects.bulk_create(
            create_list,
            ignore_conflicts=True,
        )
    except OperationalError as e:
        log.warning(f"Error creating file permissions: {e}")

        bulk_set_file_permissions(paths=paths, uwnetid=uwnetid)

[onyx patio]

For sure, monitoring the error will help you debug this one. At least in Postgres, a deadlock gives an error like

deadlock detected
DETAIL:  Process 2422 waits for ShareLock on transaction 7005; blocked by process 2421.
Process 2421 waits for ShareLock on transaction 7006; blocked by process 2422.
HINT:  See server log for query details.

And then the DB should log exactly which query was running in each transaction. It's probably similar in MySQL. That could help you nail down a test case for the problem

[lofty mural]

at this point I wonder if you should consider a "retrying" library

there's something about that recursive call in the "except" clause that gives me the willies

[onyx patio]

Interestingly, the Postgres docs recommend avoiding deadlocks first, and retrying transactions if that's not possible.

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.
Maybe there is some implementation detail of MySQL that makes deadlock scenarios more common...

[sharp girder]

In like 25 years of using Postgres I don't think I've once had a deadlock that caused a query to abort. I'm not loving working with MySQL.

[sharp girder]

Yeah, it's gross, but quick. I'm betting that this is super rare. I wouldn't be surprised if it never comes up again. I'll know if it goes into an infinite loop and can address it then. If it does go into an infinite loop the worker will be killed when it times out, so it shouldn't bring down the machine or anything.

[distant karma]

Can you swap? 😅

[sharp girder]

I would in a minute if I could. We're talking about group goals for FY2026, and I'm going to add that as a goal.

[lofty mural]

I don't know what it is about postgres makes it less likely to deadlock than MySQL 😕

[sharp girder]

For one thing Postgres doesn't lock tables on insert (and I think on updates) because of the way its transactions work.

[lofty mural]

ah, so I know sqlite locks tables sometimes; I didn't realize MySQL also does

[sharp girder]

https://www.postgresql.org/docs/current/mvcc-intro.html

[onyx patio]

I had a working concurrency test for Postgres with a SerializationFailure at https://github.com/charlesroelli/django-test-race-condition/blob/main/ledger/tests.py#L45, just tested it on MySQL on a whim and it instead failed with a deadlock, and the test runner doesn't even terminate correctly afterwards! 🤷‍♂️ And the mysql logs are not so helpful...

[lofty mural]

I dimly recall kludging up something similar, with a barrier.