mysql 5.5 python,python - 从MySQL 5.5升级到5.7后，查询更常遇到死锁

最近，我们使用AWS DMS服务将生产数据库迁移到版本从5.5升级到5.7的Amazon RDS。之后，在重复的键更新查询和更新查询中，我们的插入操作经常遇到死锁问题。而在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

当我们解释查询时，它给出了更好的执行计划。尽管如此，我们仍会遇到频繁的僵局，因此，其他查询也会变慢。

这两个查询都作为不同的API线程执行(不同的连接)

使用在MySQL DB中启用了自动提交功能的pythons Mysqldb软件包。

解释输出

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 |

+----+-------------+--------------------------+------------+-------+---------------+------+---------+-------------+--------+-------------+

最佳答案

如果这主要是一个多对多映射表，请删除id并遵循http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table中的其余建议

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

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