Deadlock innoDB

Question

I get a Deadlock found when trying to get lock; try restarting transaction error Deadlock found when trying to get lock; try restarting transaction Deadlock found when trying to get lock; try restarting transaction on my InnoDB tables. Here is the request:

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

This query also launches this using the ON UPDATE button:

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

Here's how I tried to fix 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: is this the only way to deal with a dead end? I mean, this is pretty ugly, and in any case does happen from time to time. Is there a recommended way to eliminate deadlocks?

Answer 1

Here is some good documentation on handling InnoDB locks .

PS: I had nothing else to add, so I just gave you the link.

Answer 2

You may need to play some games with transaction isolation / MVCC.

Option 1) You might want to surround the request 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 can turn off auto-messaging for your session

 SET autocommit = 0; 

Option 3) Change transaction isolation before running the query to get dirty reads

This is a stretch !!!

 SET tx_isolation = READ_COMMITTED; 

or

 SET tx_isolation = READ_UNCOMMITTED; 

Give it a try and let us know everything !!!

Answer 3

This is the correct way, as stated in the documentation:

As a rule, you should write your applications so that they are always ready to reissue a transaction if it rolls back due to a deadlock.

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