Deadlock: How to prevent Primary Key Lock in SELECT

Question

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'

Lock:

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:

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

Lock:

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:

CREATE TABLE `user` (
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

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.