Php – Avoiding MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

database-deadlocksdeadlockMySQLPHP

I sometimes gets mysql deadlock errors saying:

'Deadlock found when trying to get lock; try restarting transaction'

I have a queues table where multiple php processes are running simultaneously selecting rows from the table. However, for each process i want it grab a unique batch of rows each fetch so i don't have any overlapping rows being selected.

so i run this query: (which is the query i get the deadlock error on)

    $this->db->query("START TRANSACTION;");

    $sql = "   SELECT   mailer_queue_id
                FROM    mailer_queues
                WHERE   process_id IS NULL
                LIMIT   250 
                FOR UPDATE;";
    ...


    $sql = "UPDATE  mailer_queues
            SET     process_id = 33044,
                    status = 'COMPLETED'
            WHERE   mailer_queue_id 
                IN  (1,2,3...);";

    ...

    if($this->db->affected_rows() > 0) {
        $this->db->query("COMMIT;");       
    } else{
        $this->db->query("ROLLBACK;");      
    }

I'm also:

inserting rows to the table (with no transactions/locks) at the same time

updating rows in the table (with no transactions/locks) at the same time

deleting the rows from the table (with no transactions/locks) at the same time

As well, my updates and deletes only update and delete rows where they have a process_id assigned to them …and where i perform my transactions that "SELECT rows … FOR UPDATE" are where the process_id = null. In theory they should never be overlapping.

I'm wondering if there is a proper way to avoid these deadlocks?

Can a deadlock occur because one transaction is locking the table for too long while its selecting/update and the another process is trying to perform the same transaction and just timesout?

any help is much appreciated

Best Answer

Deadlocks occur when two or more processes requests locks in such a way that the resources being locked overlap, but occur in different orders, so that each process is waiting for a resource that's locked by another process, and that other process is waiting for a lock that the original process has open.

In real world terms, consider a construction site: You've got one screwdriver, and one screw. Two workers need to drive in a screw. Worker #1 grabs the screwdriver, and worker #2 grabs the screw. Worker #1 goes to grab the screw as well, but can't, because it's being held by worker #2. Worker #2 needs the screwdriver, but can't get it because worker #1 is holding it. So now they're deadlocked, unable to proceed, because they've got 1 of the 2 resources they need, and neither of them will be polite and "step back".

Given that you've got out-of-transaction changes occurring, it's possible that one (or more) of your updates/deletes are overlapping the locked areas you're reserving inside the transactions.

Related Solutions

MySQL – UPDATE query based on SELECT Query

You can actually do this one of two ways:

MySQL update join syntax:

UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

ANSI SQL syntax:

UPDATE tableA SET validation_check = 
    (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
        FROM tableA
        INNER JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Pick whichever one seems most natural to you.

Mysql – How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);

it will be impossible to get a deadlock.

So this is what I suggest:

Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:

Change

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

DELETE FROM onlineusers 
WHERE id IN (
    SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND 
    ORDER BY id
) u;

Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

Best Answer

Related Solutions

MySQL – UPDATE query based on SELECT Query

Mysql – How to avoid MySQL ‘Deadlock found when trying to get lock; try restarting transaction’

Related Topic