从MySQL 5.5升级到5.7之后，查询更频繁地遇到死锁

Question

最近，我们使用AWS服务将生产数据库迁移到Amazon，版本从5.5升级到5.7。之后，我们经常会遇到insert...on重复密钥更新查询和更新查询的死锁问题。而在MySQL 5.5中，这是非常小的。

例如，假设我们的表结构如下所示。

CREATE TABLE `job_notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `created_time` int(11) NOT NULL,
  `updated_time` int(11) NOT NULL,
  `notify_status` tinyint(3) DEFAULT '0'
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`,`job_id`),
) ENGINE=InnoDB AUTO_INCREMENT=58303732 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

我们的插入查询如下…

    INSERT INTO job_notification (uid, notify_status, updated_time, created_time, job_id) VALUES
('24832194',1,1571900253,1571900253,'734749'),
('24832194',1,1571900254,1571900254,'729161'),
('24832194',1,1571900255,1571900255,'713225'),
('24832194',1,1571900256,1571900256,'701897'),
('24832194',1,1571900257,1571900257,'682155'),
('24832194',1,1571900258,1571900258,'730817'),
('24832194',1,1571900259,1571900259,'717162'),
('24832194',1,1571900260,1571900260,'712884'),
('24832194',1,1571900261,1571900261,'708267'),
('24832194',1,1571900262,1571900262,'701855'),
('24832194',1,1571900263,1571900263,'702129'),
('24832194',1,1571900264,1571900264,'726738'),
('24832194',1,1571900265,1571900265,'725105'),
('24832194',1,1571900266,1571900266,'709306'),
('24832194',1,1571900267,1571900267,'702218'),
('24832194',1,1571900268,1571900268,'700966'),
('24832194',1,1571900269,1571900269,'693848'),
('24832194',1,1571900270,1571900270,'730793'),
('24832194',1,1571900271,1571900271,'729352'),
('24832194',1,1571900272,1571900272,'729043'),
('24832194',1,1571900273,1571900273,'724631'),
('24832194',1,1571900274,1571900274,'718394'),
('24832194',1,1571900275,1571900275,'711702'),
('24832194',1,1571900276,1571900276,'707765'),
('24832194',1,1571900277,1571900277,'692288'),
('24832194',1,1571900278,1571900278,'735549'),
('24832194',1,1571900279,1571900279,'730786'),
('24832194',1,1571900280,1571900280,'706814'),
('24832194',1,1571900281,1571900281,'688999'),
('24832194',1,1571900282,1571900282,'685079'),
('24832194',1,1571900283,1571900283,'686661'),
('24832194',1,1571900284,1571900284,'722110'),
('24832194',1,1571900285,1571900285,'715277'),
('24832194',1,1571900286,1571900286,'701846'),
('24832194',1,1571900287,1571900287,'730105'),
('24832194',1,1571900288,1571900288,'725579')
 ON DUPLICATE KEY UPDATE notify_status=VALUES(notify_status), updated_time=VALUES(updated_time)

我们的更新查询如下…

update job_notification set notify_status = 3 where uid = 51032194 and job_id in (616661, 656221, 386760, 189461, 944509, 591552, 154153, 538703, 971923, 125080, 722110, 715277, 701846, 725579, 686661, 685079)

这些查询在MySQL 5.5中运行良好，数据和索引的数据包大小相同，但在迁移之后，此类查询经常会出现死锁.

注:我们的系统是一个高级并发系统。innodb_deadlock_detect是禁用的。innodb_lock_wait_timeout是50岁。

innodb_buffer_pool_size是50465865728

当我们解释查询时，它给出了一个更好的执行计划。尽管如此，我们仍然会频繁地陷入死锁，而且由于这个原因，其他查询也会慢下来。

这两个查询都是使用pythons包作为不同的API线程(不同的连接)执行的，在MySQL DB中启用了自动提交。

解释输出

explain update job_notification SET notify_status = 3 where uid = 51032194 and job_id in (616661, 656221, 386760, 189461, 944509, 591552, 154153, 538703, 971923, 125080, 722110, 715277, 701846, 725579, 686661, 685079);
+----+--------+------------+------------+-------+---------------+------+-----+-------+------+----------+--------+
| id | select_type | table                    | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered |Extra       |
+----+----------+------------+------------+-------+---------------+------+---------+-------------+------+----------+----------+
|  1 | UPDATE      | job_notification | NULL       | range | uid           | uid  | 8       | const,const |   27 |   100.00 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+------+---------+-------------+--------+-------------+

Answer 1

如果这主要是一个从多到多的映射表，则清除If id并遵循http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table中的其他建议。

这将使查询在这两个系统上运行得更快。更快=更少的死锁机会。

让我们看看僵局，可能会有其他事情发生。死锁发生后立即使用SHOW ENGINE INNODB STATUS;。