We're dealing with InnoDB deadlocks that are affecting our virtual transaction system.
LATEST DETECTED DEADLOCK 150210 20:51:03 *** (1) TRANSACTION: TRANSACTION 0 4240405649, ACTIVE 0 sec, process no 1250, OS thread id 140378832058112 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s), undo log entries 1 MySQL thread id 2069114, query id 21027980 localhost 127.0.0.1 dbname Updating UPDATE users SET points = points + 7000 WHERE id = '47112' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1823 page no 860 n bits 104 index `PRIMARY` of table `dbname`.`users` trx id 0 4240405649 lock_mode X locks rec but not gap waiting [...] *** (2) TRANSACTION: TRANSACTION 0 4240405339, ACTIVE 1 sec, process no 1250, OS thread id 140378835252992 fetching rows, thread declared inside InnoDB 294 mysql tables in use 1, locked 1 683 lock struct(s), heap size 63472, 23093 row lock(s), undo log entries 22383 MySQL thread id 2069041, query id 21009690 localhost 127.0.0.1 dbname Updating UPDATE users SET session_active = 0 WHERE session_lastclick < 1423618562 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1823 page no 860 n bits 104 index `PRIMARY` of table `dbname`.`users` trx id 0 4240405339 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Essentially, we have a
users table that contains a column
session_lastclick (which is the timestamp of the user's last click) and another called
points which is the user's currency balance.
Cron is actively setting sessions to active/inactive via minutely jobs and a
users points balance could be updated by any number of actions in real time.
There are obviously application-level things to resolve. But at a database level, how should the data be segmented out to get rid of these deadlocks? A new table dedicated to
user point balances, a separate
user session table, or both?
I've to give an answer to the application level. Thanks in advanced.