SQLAlchemy / MySQL Deadlocks on serialized access
<p>I have a big problem with a deadlock in an InnoDB table used with sqlalchemy. </p>

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

<p>I have already serialized the access, but still get a deadlock error. </p>

<p>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. </p>

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

<p>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.</p>

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

<pre><code>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
</code></pre>

<p>I don't understand why the deadlocks are occurring. </p>

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

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

<blockquote>
  <p>Always be prepared to re-issue a transaction if it fails due to
  deadlock. Deadlocks are not dangerous. Just try again.</p>
  
  <p>Source: <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html</a></p>
</blockquote>

<p>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.</p>

<p>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. </p>

<p>Finally I have found a good description on stackoverflow too. After identifying the right search terms.
<a href="http://stackoverflow.com/a/2596101/5532934">http://stackoverflow.com/a/2596101/5532934</a></p>

python mysql sqlalchemy deadlock