We're dealing with InnoDB deadlocks that are affecting our virtual transaction system.

150210 20:51:03
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 dbname Updating
UPDATE users SET points = points + 7000 WHERE id = '47112'
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


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 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.

  • Is there an index on session_lastclick? – Michael - sqlbot Feb 12 '15 at 22:38
  • No, there wasn't. I've just added one. I suppose this will increase the efficiency and speed of the WHERE session_lastclick UPDATEs but I'm still looking for a permanent design solution for the deadlocks. – user58509 Feb 13 '15 at 0:10
  • Ah, but if I'm reading the deadlocks correctly, you had 23093 row locks and a lock on the primary key all held by trx 2, which suggested a full table scan was being done to find the rows to update, in the absence of that index. Adding that index should result in a lot fewer rows being locked when that "session_lastclick" query runs, reducing the chance of a deadlock. Arguably, there's no need to do that second query, since the value of session_lastclick subtracted from the current time could give you a true/false answer to whether a session is "active" whenever you need to know. – Michael - sqlbot Feb 13 '15 at 2:07
  • Ah, you're right! Bingo. I can eliminate the physical session_active database field altogether and replace it with IF(UNIX_TIMESTAMP() - session_lastclick <= 900, 1, 0) AS session_active (users active in the last 15 minutes == 900 seconds). Solved. Thanks! – user58509 Feb 13 '15 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy