SQLAlchemy / MySQL Deadlocks on serialized access

Question

I have a big problem with a deadlock in an InnoDB table used with sqlalchemy.

sqlalchemy.exc.InternalError: (mysql.connector.errors.InternalError) 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

I have already serialized the access, but still get a deadlock error.

This code is executed on the first call in every function. Every thread and process should wait here, till it gets the lock. It's simplified, as selectors are removed.

    # The work with the index -1 always exists.
    f = s.query(WorkerInProgress).with_for_update().filter(
        WorkerInProgress.offset == -1).first()

I have reduced my code to a minimal state. I am currently running only concurrent calls on the method next_slice. Session handling, rollback and deadloc handling are handled outside.

I get deadlocks even all access is serialized. I did tried to increment a retry counter in the offset == -1 entity as well.

def next_slice(self, s, processgroup_id, itemcount):
    f = s.query(WorkerInProgress).with_for_update().filter(
        WorkerInProgress.offset == -1).first()

    #Take first matching object if available / Maybe some workers failed
    item = s.query(WorkerInProgress).with_for_update().filter(
        WorkerInProgress.processgroup_id != processgroup_id,
        WorkerInProgress.processgroup_id != 'finished',
        WorkerInProgress.processgroup_id != 'finished!locked',
        WorkerInProgress.offset != -1
        ).order_by(WorkerInProgress.offset.asc()).limit(1).first()

    # *****
    # Some code is missing here. as it's not executed in my testcase

    # Fetch the latest item and add a new one 
    item = s.query(WorkerInProgress).with_for_update().order_by(
        WorkerInProgress.offset.desc()).limit(1).first()     

    new = WorkerInProgress()
    new.offset = item.offset + item.count
    new.count = itemcount
    new.maxtries = 3
    new.processgroup_id = processgroup_id
    s.add(new)
    s.commit()
    return new.offset, new.count

I don't understand why the deadlocks are occurring.

I have reduced deadlock by fetching all items in one query, but still get deadlocks. Perhaps someone can help me.

score 6 · Accepted Answer · edited May 23 '17 at 12:32

Finally I solved my problem. It's all in the documentation, but I have to understand it first.

Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.

Source: http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html

I have solved my problem by changing the architecture of this part. I still get a lot of deadlocks, but they appear almost in the short running methods. I have splitted my worker table to a locking and an non locking part. The actions on the locking part are now very short and no data is handling during the get_slice, finish_slice and fail_slice operations.

The transaction part with data handling are now in a non locking part and without concurrent access to table rows. The results are stored in finish_slice and fail_slice to the locking table.

Finally I have found a good description on stackoverflow too. After identifying the right search terms. https://stackoverflow.com/a/2596101/5532934

SQLAlchemy / MySQL Deadlocks on serialized access

1 Answers1