In MySQL 5.7 we are running tracking queries every day at 0:00.

Lately, these are causing deadlocks with live calls that happen simultaneously which causes issues in our live service. (Or maybe I just started to notice this now..)

Tracking Call:

INSERT INTO track_daily (date, dau, mau, chips, subscriptions, trueskill,
                         results_canceled, origin)
SELECT DATE_SUB('17-05-31', INTERVAL 1 DAY), count(*), 14, 315, 1,
       if(count(*) = 0, 0, avg(mean - 3 * stddev)), 0.0299, 'facebook'
FROM   user
WHERE  lastLogin >= DATE_SUB('17-05-31', INTERVAL 1 DAY)
AND    user.origin = 'facebook'


RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675275 lock mode S

Service Call:

SET    chips = chips + -1, chipsWon = chipsWon + 0
WHERE  uid = '1448963' AND chips >= --1


RECORD LOCKS space id 1084 page no 6776 n bits 176 index PRIMARY of table data.user trx id 6727675426 lock_mode X locks rec but not gap waiting

I understand this is happening because both queries are locking the PRIMARY key of the user table, but I do not understand why the tracking call requires this lock. Can someone give me a tip how to optimize the tracking call so it does not lock the PRIMARY anymore?

Edit: The result from SHOW CREATE TABLE:

    `uid` int(10) unsigned NOT NULL,
    `mean` double NOT NULL DEFAULT ''25'',
    `stddev` double NOT NULL DEFAULT ''8.33333333333333'',
    `trust` double NOT NULL DEFAULT ''1'',
    `roundsPlayed` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedDaily` smallint(5) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedLeague` int(10) unsigned NOT NULL DEFAULT ''0'',
    `roundsPlayedChips` int(10) unsigned NOT NULL DEFAULT ''0'',
    `dailyScore` int(11) NOT NULL DEFAULT ''0'',
    `leagueScore` int(11) NOT NULL DEFAULT ''0'',
    `alltimeScore` int(11) NOT NULL DEFAULT ''0'',
    `displayName` varchar(255) NOT NULL DEFAULT ''Neuling'',
    `imageUrl` varchar(255) NOT NULL,
    `chips` mediumint(8) unsigned NOT NULL DEFAULT ''17'',
    `chipsWon` mediumint(8) NOT NULL DEFAULT ''0'',
    `lastLogin` timestamp NULL DEFAULT NULL,
    `registered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `origin` varchar(20) NOT NULL,
    `banned` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
    PRIMARY KEY (`uid`),
    KEY `leagueScore` (`leagueScore`),
    KEY `dailyScore` (`dailyScore`),
    KEY `chipsWon` (`chipsWon`),
    KEY `user_roundsPlayedLeague_idx` (`roundsPlayedLeague`),
    KEY `user_roundsPlayedChips_idx` (`roundsPlayedChips`),
    KEY `user_roundsPlayed_idx` (`roundsPlayed`)
  • When this condition occurs, what does "show processlist" look like? Does it show Waiting for metadata lock? – thatsaru May 31 '17 at 13:01
  • I haven't observed this live, yet. Can I record that or do I have to call SHOW PROCESSLIST in the right moment? I could do a night shift and try to observe it. – rewb0rn May 31 '17 at 14:40
  • It'll have to be done in the right moment using "show processlist". – thatsaru May 31 '17 at 16:32

Making queries faster is a way to minimize deadlocks, if not avoid them.

INDEX(origin, lastLogin)

should help the first query.

The UPDATE should be helped by

INDEX(uid, chips)

Please provide SHOW CREATE TABLE if you wish to discuss thing further.

How many cron jobs run at midnight? If more than one, stagger them. Otherwise the buffalo will stumble over each other.

If you are collecting "yesterday's" data, you should include a stopper -- else it could include a few records from "today":

WHERE lastLogin >= '2017-05-31' - INTERVAL 1 DAY
  AND lastLogin  < '2017-05-31'

With that, it won't matter if you schedule it a few minutes after midnight.

I cringed at --1 and + -1, but I guess they work OK.

  • Thanks, I added the question with the SHOW CREATE TABLE output, let me know if there is an obvious problem. I will also add the INDEX you suggested. The cronjob calls all trackers sequentially, so that should not be a problem. The stopper is a good point and should be added. I was also thinking to edit out the --1 and + -1 because they are a result of variable binding (WHERE chips > -:value), but then figured it's best to attach the original unaltered log output. I already upvoted your answer, but it's not visible yet due to my status. – rewb0rn Jun 1 '17 at 7:59
  • We also have one tracking call that leaves out the AND origin = :x part to track activity of all users. Should I also add an INDEX(lastLogin) for that case? – rewb0rn Jun 1 '17 at 8:13
  • @rewb0rn - Yes. If that query is common enough, then add INDEX(lastLogin). (There is no way to make one index serve both tasks -- because lastLogin is used as a "range", not "=".) – Rick James Jun 2 '17 at 1:21
  • Tonight was the first night where these deadlocks are no longer showing up after adding the suggested index. Some other cases are popping up now but I want to investigate them myself before I continue to ask stupid questions. Thanks for your help! – rewb0rn Jun 2 '17 at 7:34
  • @rewb0rn - "stupid" question, no. "common" question, yes. – Rick James Jun 2 '17 at 14:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.