CODEWITHSUNDEEP
phpmysqlinnodbdeadlock
Silver Light
Silver Light

Reputation: 45972

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?

Upvotes: 7

Views: 5618

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

You may have to play some games with the Transaction Isolation/MVCC features

Option 1) You may want to surround the query in a begin commit block

BEGIN;

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

< Any actions in the trigger would be part of the transaction >

COMMIT;

Option 2) You may want to disable autocommit for your session

SET autocommit = 0;

Option 3) Change the transaction isolation before launching the query to expose dirty reads

This one is a stretch !!!

SET tx_isolation = READ_COMMITTED;

or

SET tx_isolation = READ_UNCOMMITTED;

Give it a Try and let us all know !!!

Upvotes: 2

ggiroux
ggiroux

Reputation: 6724

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.

Upvotes: 2

Zimbabao
Zimbabao

Reputation: 8250

Here is good documentation about handling InnoDB deadlocks.

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

Upvotes: 2

Related Questions