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?
Here is good documentation about handling InnoDB deadlocks.
PS: I didn't have anything more to add so just gave you the link.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With