Mysql
如何避免此查詢的死鎖？
我正在執行一個 MySql 5.7 數據庫，並且我有一個查詢導致死鎖，因為它在多個執行緒中執行。
我試圖弄清楚如何避免這種僵局，但到目前為止我還無法做到。我什至嘗試過：
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED查詢執行看起來像這樣
UPDATE yjob_userrank target JOIN ( SELECT userranks.id, @`row_number` := IF(@job_id = yjob_id, @row_number+1, 1) AS `row_number`, @job_id := yjob_id AS job_id FROM ( SELECT yjob_userrank.id, yjob_id, rank_wo_date + users.`rank` as combined_rank, is_match FROM yjob_userrank JOIN users ON users.id = yjob_userrank.user_id WHERE yjob_id IN (1716, 1667) ) as userranks ORDER BY yjob_id, is_match DESC, combined_rank DESC ) source on target.id = source.id SET relative_rank = `row_number`架構是
create table yjob_userrank ( id int unsigned auto_increment primary key, user_id int unsigned not null, yjob_id int unsigned not null, rank_w_date decimal(5,2) default 0.00 not null, rank_wo_date decimal(5,2) default 0.00 not null, is_match tinyint unsigned not null, relative_rank smallint(6) null, created_at timestamp null, updated_at timestamp null, constraint yjob_userrank_yjob_id_user_id_unique unique (yjob_id, user_id) ) collate=utf8_unicode_ci; create index yjob_userrank_user_id_index on yjob_userrank (user_id); create index yjob_userrank_yjob_id_is_match_rank_wo_date_index on yjob_userrank (yjob_id, is_match, rank_wo_date);此程式碼在 laravel 應用程序中執行，方法如下所示
private function calculateRelativeRanks(array $jobIds) { $jobIdsWhere = rtrim(str_repeat("?,", count($jobIds)), ','); $this->db->statement('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'); $this->db->transaction(function (ConnectionInterface $db) use ($jobIds, $jobIdsWhere) { $db->statement(" UPDATE yjob_userrank target JOIN ( SELECT userranks.id, @`row_number` := IF(@job_id = yjob_id, @row_number+1, 1) AS `row_number`, @job_id := yjob_id AS job_id FROM ( SELECT yjob_userrank.id, yjob_id, rank_wo_date + users.`rank` as combined_rank, is_match FROM yjob_userrank JOIN users ON users.id = yjob_userrank.user_id WHERE yjob_id IN ($jobIdsWhere) ) as userranks ORDER BY yjob_id, is_match DESC, combined_rank DESC ) source on target.id = source.id SET relative_rank = `row_number` ", $jobIds); }, 3); }該查詢僅更新 field
relative_rank，但它不會在其選擇部分查看該欄位。我可以通過讀取未送出的數據來處理一些數據不一致，但即使將隔離級別更改為READ UNCOMMITTED，也沒有修復死鎖。通過執行
SHOW ENGINE INNODB STATUS;塊LATEST DETECTED DEADLOCK顯示以下要點Explain log of the query +----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+ | 1 | PRIMARY | <derived2> | | system | | | | | 1 | 100 | Using filesort | | 1 | PRIMARY | <derived3> | | ALL | | | | | 600 | 100 | | | 3 | DERIVED | yjob_userrank | | range | yjob_userrank_yjob_id_user_id_unique,yjob_userrank_user_id_index,yjob_userrank_yjob_id_is_match_rank_wo_date_index | yjob_userrank_yjob_id_user_id_unique | 4 | | 600 | 100 | Using index condition | | 3 | DERIVED | users | | eq_ref | PRIMARY | PRIMARY | 4 | worksome.yjob_userrank.user_id | 1 | 100 | | | 2 | DERIVED | | | | | | | | | | No tables used | +----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+由於數據庫是 MySql 5.7，我沒有 Window 功能，所以它基本上是鏡像該功能。如果我有 Window 支持，查詢將如下所示：
UPDATE yjob_userrank target JOIN ( SELECT yjob_userrank.id, yjob_id, ROW_NUMBER() over (w) as `relative_rank` FROM yjob_userrank JOIN users ON users.id = yjob_userrank.user_id WHERE yjob_id IN (1716, 1667) WINDOW w AS (PARTITION BY yjob_id ORDER BY yjob_id, is_match DESC, rank_wo_date + users.`rank` DESC) ) source on target.id = source.id SET target.relative_rank = source.relative_rank WHERE target.yjob_id IN (1716, 1667)更新 1： 我根據@Kondybas 的建議將查詢減少到以下內容，這解決了我的死鎖。
UPDATE yjob_userrank AS yjur SET relative_rank = @`row_number` := IF(@job_id = yjob_id, @row_number+1, 1), yjob_id = @job_id := yjur.yjob_id WHERE yjur.yjob_id IN (1716, 1667) ORDER BY yjur.yjob_id , yjur.is_match DESC , yjur.rank_wo_date + (SELECT users.`rank` FROM users WHERE users.id = yjur.user_id) DESC;
您的查詢太複雜，並且涉及太多嵌套循環，無法進行連接。還有一些程式碼部分根本沒有效果。我認為可以這樣簡化：
UPDATE yjob_userrank AS target JOIN ( SELECT yjob_userrank.id AS id , @job_id := yjob_id AS job_id , IF(@job_id = yjob_id, @row_number := @row_number+1, 1) AS row_number , rank_wo_date + users.rank AS combined_rank FROM yjob_userrank AS yjur JOIN users AS u ON u.id = yjur.user_id WHERE yjob_id IN (29,30 ...) -- only one list required AND @job_id = 0 -- not a NULL for correct comparison AND @row_number = 0 -- not a NULL for correct comparison ORDER BY yjob_id ASC , is_match DESC ) AS source ON target.id = source.id ORDER BY source.yjob_id ASC , source.combined_rank DESC SET relative_rank = source.row_number我懷疑查詢是否完全等同於您的查詢，但是您可以檢查它是否會產生相同的行集，如果
UPDATE被替換SELECT並且最終SET被註釋掉（只是將破壞性查詢轉換為非破壞性形式）。yjob_id確實，您必須使用較小的 s 集進行測試。