Sql Simultaneous Transactions Ignore Each Other's Locks??? Deadlock [innodb, Python]

Good day! I've run into a head burner. My client requires me to repurpose a python program to work with MySQL instead of Microsoft's SQL Server. I'm having trouble finding an equiv

Solution 1:

(This may not address your question, but it is a suggestion that won't fit in a Comment.)

Do all the updates at once:

UPDATE job SET
        status =%s,
        jobUUID   = IFNULL(jobUUID, UUID()),
        dateAdded = IFNULL(dateAdded, NOW())
    WHERE jobID =%s
    LIMIT 1

You may be able to get jobID by using LAST_INSERT_ID(jobID), thereby avoiding the SELECT.

Solution 2:

SELECT... FOR UPDATE isolates transactions at different levels depending on your configuration. You can find more information https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read here.

But the most important thing in your code is that you have to be using DIFFERENT sessions for different transactions.

As stated here https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html. If you run another transaction in the same session before a transaction is finished, it will be implicitly committed and it's what is leading to random results in your code.

What you are doing right now in your code is same as running two transactions in one terminal which would be not much different to just running everything in non-transaction.

You would need to create transactions in different connections through methods such as pooling in order to simulate different sessions.

Solution 3:

After adding some time.sleep statements in different parts of the transaction, I realized that the problem has got nothing to do with Alice and Barry executing simultaneously or ignoring each other's locks.

Without the sleep statements, it was too fast to see what was going on. The real issue is that Barry reads OLD data in his SELECT... FOR UPDATE, even after Alice's COMMIT which updates the job status, leaving him to take up the same job immediately after Alice releases the lock.

As this is a completely different issue, I've reposted the question with a different explanation and more relevant code samples here: SELECT... FOR UPDATE selecting old data after a commit

I'm sorry this couldn't help you. I've yet to find the problem myself.