MySQL Deadlock Error

Question

My web app run the following query likely at most 1-2 times/second, depending on user traffic:

UPDATE `click_rollups` 
   SET `clicks` = `clicks` + 1, `last_updated` = ? 
   WHERE `camp_id` = ? 
     AND `country` = ? 
     AND `clicks` < ? 
     AND `time_created` = ?

Our logs show this error is sometimes given:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

However, click_rollups is only used once in a write context in this transaction, so I can't imagine a way that a deadlock could occur. It's queried only once elsewhere in the app, using only SELECTs.

Would this therefore imply that the deadlock from these two separate transactions (the update and the select-only) are causing the issue, as each separate transaction only uses this table once (and the queries that use this table don't reference any other tables)? Or could there be row-level locking issues that could mean one of the transactions could be getting a deadlock with other occurrences of the same transaction?

Answer 1

After doing some more reading I found that, as InnoDB does use row-level locking, deadlocks can occur when just inserting or updating a single row as the actions are not atomic. I ran:

SHOW ENGINE INNODB STATUS

to find information on the last deadlock. I found:

------------------------
LATEST DETECTED DEADLOCK
------------------------
140106 17:22:41
*** (1) TRANSACTION:
TRANSACTION 63EB5222A, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 9 lock struct(s), heap size 3112, 6 row lock(s), undo log entries 2
MySQL thread id 4304350, OS thread handle 0x7fd3b74d3700, query id 173460207 192.168.0.2 sharecash Updating
UPDATE `click_rollups` SET `clicks` = `clicks` + 1, `last_updated` = '1389046961' WHERE `camp_id` = '27739' AND `country` = 'US' AND `clicks` < '1000' AND `time_created` = '1389046866'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 186 page no 407 n bits 1272 index `country` of table `sharecash`.`click_rollups` trx id 63EB5222A lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 63EB52225, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
177 lock struct(s), heap size 31160, 17786 row lock(s), undo log entries 2
MySQL thread id 4304349, OS thread handle 0x7fd6961c8700, query id 173460194 192.168.0.1 sharecash Updating
UPDATE `click_rollups` SET `clicks` = `clicks` + 1, `last_updated` = '1389046961' WHERE `camp_id` = '30949' AND `country` = 'US' AND `clicks` < '1000' AND `time_created` = '1388964767'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 186 page no 407 n bits 1272 index `country` of table `sharecash`.`click_rollups` trx id 63EB52225 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 186 page no 512 n bits 384 index `PRIMARY` of table `sharecash`.`click_rollups` trx id 63EB52225 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)

You can see that the two queries that are causing the deadlocks are actually the same exact ones. It shows that there are also different parameters for the columns in the WHERE clause, so the actual rows that are being locked are different, which seemed a bit counter-intuitive to me - how could operations on different sets of rows cause a deadlock?

The answer seems to be that the deadlock is arising from the query engine locking entries in the indexing structures. If you look at the output above, you can see that one transaction has a lock on a certain part of a certain page in the country index and needs a lock on part of the primary key index, while the other transaction is essentially the opposite case.

An invariant in this part of our app that only one row would ever have less than 1000 clicks, so I believe by fixing that issue the deadlock problem will be minimized, as there would be overall less locking done. The MySQL documentation suggests coding your applications to always re-issue transactions in the case of a rollback due to a deadlock, which would prevent this issue for causing pages to error. However, if anyone has any other ideas on how to actually avoid these deadlocks, again, please post them in the comments!

EDIT -

The country index did not need to be used by the transaction, as for each camp_id value there were only a handful (usually just 1) different values of country, each of which only corresponded to one row. I've added an index hint to the query to make it stop using this index, and the issue is now fixed without any performance hit (probably a small gain).