Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Handling innoDB deadlock

I've been getting a Deadlock found when trying to get lock; try restarting transaction error on my InnoDB tables. Here is the query:

UPDATE views 
SET visit_cnt = visit_cnt + 1 
WHERE visit_day = DATE(NOW()) 
AND article_id = '4838'

This query also triggers this via ON UPDATE trigger:

UPDATE articles 
SET views = views + 1 
WHERE id = NEW.article.id

Here is how I tried to fixed it:

$attempts_left = 5;
do
{
    mysql_query ($query);

    // if we found a deadlock, we will try this query 4 more times   
    if (mysql_errno () == 1213) { // 1213 - deadlock error
        $deadlocked = true;
        $attempts_left --;
    }
    else{
        $deadlocked = false;
    }
}
while($deadlocked && $attempts_left > 0);

My question: is this the only way to handle a deadlock? I mean this is quite ugly and deadlocks happen time to time anyway. Is there any recommended way to fix deadlocks?

like image 389
Silver Light Avatar asked Mar 15 '11 15:03

Silver Light


2 Answers

Here is good documentation about handling InnoDB deadlocks.

PS: I didn't have anything more to add so just gave you the link.

like image 153
Zimbabao Avatar answered Sep 19 '22 08:09

Zimbabao


That's the proper way, as the documentation states:

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

If you want to reduce the occurrence of deadlocks, you must show us the tables DDL and indexes.

like image 32
ggiroux Avatar answered Sep 21 '22 08:09

ggiroux