CODEWITHSUNDEEP
phpmysqldeadlockdatabase-deadlocks
dez
dez

Reputation: 2265

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

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

Upvotes: 1

Views: 14634

Answers (3)

amitchhajer
amitchhajer

Reputation: 12820

I think everyone on net has explained very well about the deadlock. Mysql provide very good log to check all the last dead lock happened and which queries were stuck at that time. Check this mysql documentation page and search for LATEST DETECTED DEADLOCK its a great logs, helped finding many subtle deadlocks.

Upvotes: 2

dar7yl
dar7yl

Reputation: 3747

You might want to try LOCK TABLES before starting the transaction, thereby assuring you have explicit control over the tables. The lock will wait until all activity on the particular tables has completed.

Upvotes: 4

Marc B
Marc B

Reputation: 360562

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.

Upvotes: 7

Related Questions