從 MySQL 5.5 升級到 5.7 後查詢更頻繁地遇到死鎖

最近，我們使用 AWS DMS 服務將生產數據庫遷移到 Amazon RDS，並將版本從 5.5 升級到 5.7。在那之後，我們經常在重複的鍵更新查詢和更新查詢上遇到我們插入的死鎖問題。而在 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 中執行良好，具有相同的數據包大小和索引，但在遷移後，此類查詢經常出現死鎖……

NB：我們的系統是高級並發系統。 innodb_deadlock_detect被禁用。innodb_lock_wait_timeout是 50。

當我們解釋查詢時，它給出了更好的執行計劃。儘管如此，我們還是經常遇到死鎖，因此其他查詢也會變慢。

解釋輸出

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

避免這種死鎖的唯一方法是確保所有數據修改語句以相同的順序處理行，例如按ORDER BY uid, job_id順序。

使用INSERT可以輕鬆完成的語句，但使用UPDATE語句取決於數據庫使用的執行計劃。也許您可以通過使用某些索引並對IN列表進行排序來做一些事情；你將不得不進行實驗。

如果沒有辦法控制更新順序，你唯一的希望就是減少批量大小。

這個不錯的文章很好地解釋了它。

死鎖是由於 mysql 完成的間隙鎖定而發生的。間隙鎖定有幾個原因，在這種特殊情況下，它與保留索引上的唯一鍵約束有關。情況以這種方式呈現給我們：列上有一個唯一鍵約束，我們正在執行插入操作。Mysql 必須確保它所佔用的鎖足以防止另一個並發插入添加具有相同鍵的記錄，從而打破唯一鍵約束。

更多關於間隙鎖的資訊。

使用唯一索引鎖定行以搜尋唯一行的語句不需要間隙鎖定

這些問答可能對您有所幫助，

https://stackoverflow.com/a/32502736

https://dba.stackexchange.com/a/87004/194233

https://dba.stackexchange.com/a/203133/194233

您可以嘗試使用單語句與批量插入。還刪除自動增量並將連接值用作主要值。

引用自：https://dba.stackexchange.com/questions/251922