Mysql

MySQL InnoDB 即使在 READ COMMITTED 中也會在刪除時鎖定主鍵

  • September 4, 2017

前言

DELETE我們的應用程序執行多個並行執行查詢的執行緒。查詢影響孤立的數據，即不應該有可能DELETE在不同執行緒的相同行上發生並發。DELETE但是，根據文件，MySQL 對語句使用所謂的“下一個鍵”鎖，它會鎖定匹配鍵和一些間隙。這會導致死鎖，我們發現的唯一解決方案是使用READ COMMITTED隔離級別。

問題

使用大量表執行複雜DELETE語句時會出現問題。在特定情況下，我們有一個只有兩行的帶有警告的表，但是查詢需要從兩個單獨的ed 表JOIN中刪除屬於某些特定實體的所有警告。INNER JOIN查詢如下：

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
  ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
  ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=? AND dp.dirty_data=1

當 day_position 表足夠大時（在我的測試案例中，有 1448 行），那麼任何事務即使具有READ COMMITTED隔離模式也會阻塞整個 proc_warnings表。

這個問題總是在這個範例數據上重現 - http://yadi.sk/d/QDuwBtpW1BxB9在 MySQL 5.1（在 5.1.59 上檢查）和 MySQL 5.5（在 MySQL 5.5.24 上檢查）。

編輯：連結的範例數據還包含查詢表的架構和索引，為方便起見，在此處複製：

CREATE TABLE  `proc_warnings` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `transaction_id` int(10) unsigned NOT NULL,
   `warning` varchar(2048) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `proc_warnings__transaction` (`transaction_id`)
);

CREATE TABLE  `day_position` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `transaction_id` int(10) unsigned DEFAULT NULL,
   `sort_index` int(11) DEFAULT NULL,
   `ivehicle_day_id` int(10) unsigned DEFAULT NULL,
   `dirty_data` tinyint(4) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `day_position__trans` (`transaction_id`),
   KEY `day_position__is` (`ivehicle_day_id`,`sort_index`),
   KEY `day_position__id` (`ivehicle_day_id`,`dirty_data`)
) ;

CREATE TABLE  `ivehicle_days` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `d` date DEFAULT NULL,
   `sort_index` int(11) DEFAULT NULL,
   `ivehicle_id` int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `ivehicle_days__is` (`ivehicle_id`,`sort_index`),
   KEY `ivehicle_days__d` (`d`)
);

每個事務的查詢如下：

  • 交易一
set transaction isolation level read committed;
set autocommit=0;
begin;
DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;
  • 交易2
set transaction isolation level read committed;
set autocommit=0;
begin;
DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=13 AND dp.dirty_data=1;

其中一個總是失敗，並出現“超過鎖定等待超時…”錯誤。其中information_schema.innodb_trx包含以下行：

| trx_id     | trx_state   | trx_started           | trx_requested_lock_id  | trx_wait_started      | trx_wait | trx_mysql_thread_id | trx_query |
| '1A2973A4' | 'LOCK WAIT' | '2012-12-12 20:03:25' | '1A2973A4:0:3172298:2' | '2012-12-12 20:03:25' | '2'      | '3089'              | 'DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1' |
| '1A296F67' | 'RUNNING'   | '2012-12-12 19:58:02' | NULL                   | NULL | '7' | '3087' | NULL |

information_schema.innodb_locks

| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |

正如我所看到的，兩個查詢都希望X在主鍵 = 53 的行上獲得排他鎖。但是，它們都不能從proc_warnings表中刪除行。我只是不明白為什麼索引被鎖定。此外，當proc_warnings表為空或day_position表包含較少的行數（即一百行）時，索引不會被鎖定。

進一步的調查是執行EXPLAIN類似的SELECT查詢。它表明查詢優化器不使用索引來查詢proc_warnings表，這是我能想像它阻塞整個主鍵索引的唯一原因。

簡化案例

當只有兩個具有幾條記錄的表時，也可以在更簡單的情況下重現問題，但子表在父表 ref 列上沒有索引。

創建parent表

CREATE TABLE `parent` (
 `id` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB

創建child表

CREATE TABLE `child` (
 `id` int(10) unsigned NOT NULL,
 `parent_id` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB

填寫表格

INSERT INTO `parent` (id) VALUES (1), (2);
INSERT INTO `child` (id, parent_id) VALUES (1, NULL), (2, NULL);

在兩個並行事務中進行測試：

  • 交易一
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c 
 INNER JOIN parent p ON p.id = c.parent_id 
WHERE p.id = 1;
  • 交易2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
BEGIN;
DELETE c FROM child c 
 INNER JOIN parent p ON p.id = c.parent_id 
WHERE p.id = 2;

這兩種情況的共同點是 MySQL 不使用索引。我相信這是整個表鎖定的原因。

我們的解決方案

我們現在可以看到的唯一解決方案是將預設鎖定等待超時從 50 秒增加到 500 秒，以讓執行緒完成清理。然後保持手指交叉。

任何幫助表示讚賞。

新答案（MySQL 風格的動態 SQL）：好的，這個以另一張海報描述的方式解決了這個問題 - 顛倒獲取互不兼容的排他鎖的順序，這樣無論發生多少，它們只發生在事務執行結束時的最短時間。

這是通過將語句的讀取部分分成它自己的選擇語句並動態生成刪除語句來實現的，該語句將由於語句出現的順序而被強制最後執行，並且僅影響 proc_warnings 表。

sql fiddle 提供了一個展示：

此連結顯示帶有範例數據的架構，以及對匹配的行的簡單查詢ivehicle_id=2。結果為 2 行，因為它們都沒有被刪除。

此連結顯示相同的模式、範例數據，但將值 2 傳遞給 DeleteEntries 儲存程序，告訴 SP 刪除proc_warnings條目ivehicle_id=2。行的簡單查詢不返回任何結果，因為它們都已被成功刪除。展示連結僅展示程式碼按預期刪除。擁有合適測試環境的使用者可以評論這是否解決了執行緒阻塞的問題。

為方便起見，這裡也是程式碼：

CREATE PROCEDURE DeleteEntries (input_vid INT)
BEGIN

   SELECT @idstring:= '';
   SELECT @idnum:= 0;
   SELECT @del_stmt:= '';

   SELECT @idnum:= @idnum+1 idnum_col, @idstring:= CONCAT(@idstring, CASE WHEN CHARACTER_LENGTH(@idstring) > 0 THEN ',' ELSE '' END, CAST(id AS CHAR(10))) idstring_col
   FROM proc_warnings
   WHERE EXISTS (
       SELECT 0
       FROM day_position
       WHERE day_position.transaction_id = proc_warnings.transaction_id
       AND day_position.dirty_data = 1
       AND EXISTS (
           SELECT 0
           FROM ivehicle_days
           WHERE ivehicle_days.id = day_position.ivehicle_day_id
           AND ivehicle_days.ivehicle_id = input_vid
       )
   )
   ORDER BY idnum_col DESC
   LIMIT 1;

   IF (@idnum > 0) THEN
       SELECT @del_stmt:= CONCAT('DELETE FROM proc_warnings WHERE id IN (', @idstring, ');');

       PREPARE del_stmt_hndl FROM @del_stmt;
       EXECUTE del_stmt_hndl;
       DEALLOCATE PREPARE del_stmt_hndl;
   END IF;
END;

這是從事務中呼叫程序的語法：

CALL DeleteEntries(2);

ORIGINAL ANSWER（仍然認為它不是太破舊）看起來像2個問題：1）緩慢的查詢2）意外的鎖定行為

關於問題 #1，慢查詢通常通過相同的兩種技術在串聯查詢語句簡化和有用的索引添加或修改中得到解決。您自己已經建立了與索引的連接——沒有它們，優化器將無法搜尋有限的一組行來處理，並且每個表中的每一行乘以每個額外的行掃描必須完成的額外工作量。

在看到 SCHEMA 和索引的文章後進行了修訂：但我想通過確保您擁有良好的索引配置，您將獲得最大的查詢性能優勢。為此，您可以尋求更好的刪除性能，甚至可能是更好的刪除性能，同時權衡更大的索引，並且在添加了額外索引結構的相同表上可能會顯著降低插入性能。

更好一些：

CREATE TABLE  `day_position` (
   ...,
   KEY `day_position__id_rvrsd` (`dirty_data`, `ivehicle_day_id`)

) ;


CREATE TABLE  `ivehicle_days` (
   ...,
   KEY `ivehicle_days__vid_no_sort_index` (`ivehicle_id`)
);

在這裡也進行了修訂：由於執行所需的時間與執行時間一樣長，因此我會將dirty_data 保留在索引中，並且當我按索引順序將其放置在 ivehicle_day_id 之後時，我肯定也弄錯了 - 它應該是第一個。

但是，如果我在這一點上親自動手，因為必須有大量數據才能讓它花這麼長時間，我會選擇所有覆蓋索引，以確保我得到最好的索引我的故障排除時間可以買到，如果沒有其他辦法可以排除這部分問題的話。

最佳/覆蓋指數：

CREATE TABLE  `day_position` (
   ...,
   KEY `day_position__id_rvrsd_trnsid_cvrng` (`dirty_data`, `ivehicle_day_id`, `transaction_id`)
) ;

CREATE TABLE  `ivehicle_days` (
   ...,
   UNIQUE KEY `ivehicle_days__vid_id_cvrng` (ivehicle_id, id)
);

CREATE TABLE  `proc_warnings` (

   .., /*rename primary key*/
   CONSTRAINT pk_proc_warnings PRIMARY KEY (id),
   UNIQUE KEY `proc_warnings__transaction_id_id_cvrng` (`transaction_id`, `id`)
);

最後兩個更改建議尋求兩個性能優化目標：

1）如果連續訪問的表的搜尋鍵與目前訪問的表返回的聚集鍵結果不同，我們消除了本來需要做的事情在聚集索引上進行第二組 index-seek-with-scan 操作

  1. 如果不是後者，那麼至少優化器仍有可能選擇更有效的連接算法，因為索引將保持所需的連接鍵按排序順序。

您的查詢似乎已盡可能簡化（複製到此處以防稍後對其進行編輯）：

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;

當然，除非有一些關於寫入連接順序的東西會影響查詢優化器的執行方式，在這種情況下，您可以嘗試其他人提供的一些重寫建議，包括可能包含索引提示的這個（可選）：

DELETE FROM proc_warnings
FORCE INDEX (`proc_warnings__transaction_id_id_cvrng`, `pk_proc_warnings`)
WHERE EXISTS (
   SELECT 0
   FROM day_position
   FORCE INDEX (`day_position__id_rvrsd_trnsid_cvrng`)  
   WHERE day_position.transaction_id = proc_warnings.transaction_id
   AND day_position.dirty_data = 1
   AND EXISTS (
       SELECT 0
       FROM ivehicle_days
       FORCE INDEX (`ivehicle_days__vid_id_cvrng`)  
       WHERE ivehicle_days.id = day_position.ivehicle_day_id
       AND ivehicle_days.ivehicle_id = ?
   )
);

至於 #2，意外的鎖定行為。

正如我所看到的，這兩個查詢都希望在主鍵 = 53 的行上使用排他 X 鎖。但是，它們都不能從 proc_warnings 表中刪除行。我只是不明白為什麼索引被鎖定。

我猜這將是鎖定的索引，因為要鎖定的數據行位於聚集索引中，即單行數據本身駐留在索引中。

它將被鎖定，因為：

1）根據http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

…a DELETE 通常在 SQL 語句處理過程中掃描的每條索引記錄上設置記錄鎖。語句中是否存在將排除該行的 WHERE 條件並不重要。InnoDB 不記得確切的 WHERE 條件，而只知道掃描了哪些索引範圍。

您在上面還提到：

…對我來說，READ COMMITTED 的主要特點是它如何處理鎖。它應該釋放不匹配行的索引鎖，但它沒有。

並為此提供了以下參考：http:

//dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

與您的狀態相同，除了根據相同的參考，有一個條件可以釋放鎖：

此外，在 MySQL 評估 WHERE 條件後，不匹配行的記錄鎖將被釋放。

本手冊頁http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html也重申了這一點

使用 READ COMMITTED 隔離級別或啟用 innodb_locks_unsafe_for_binlog 還有其他影響：在 MySQL 評估 WHERE 條件後釋放不匹配行的記錄鎖。

因此，我們被告知必須在釋放鎖之前評估 WHERE 條件。不幸的是，我們沒有被告知何時評估 WHERE 條件，並且它可能會從一個計劃更改為優化器創建的另一個計劃。但它確實告訴我們，鎖的釋放，在某種程度上取決於查詢執行的性能，正如我們上面討論的，其優化取決於語句的仔細編寫和索引的明智使用。它也可以通過更好的表格設計來改進，但這可能最好留給一個單獨的問題。

此外，當 proc_warnings 表為空時，索引也不會被鎖定

如果沒有，數據庫無法鎖定索引中的記錄。

此外，當…day_position 表包含較少的行數（即一百行）時，索引不會被鎖定。

這可能意味著很多事情，例如但可能不限於：由於統計數據的變化而導致的執行計劃不同，由於數據集小得多而執行速度更快而導致的鎖定過於簡短/加入操作。

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

相關問答

Mysql

Mysql中已送出讀隔離級別的間隙鎖定

  • May 13, 2019
檢視問答
Mysql

SELECT INTO OUTFILE 與 INSERT INTO … SELECT

  • December 17, 2014
檢視問答
Mysql

InnoDB 和隔離級別 - 可重複讀取不是一件壞事嗎？

  • December 2, 2014
檢視問答
Mysql

MySQL 一致的非鎖定讀取與 INSERT … SELECT

  • August 8, 2014
檢視問答
Mysql

關於緩衝池實例的大小和數量是否有經驗法則？

  • April 3, 2014
檢視問答
Mysql

Mysql 鎖定 - 如何為每個 userId 在數據庫中獲取鎖定

  • December 18, 2021
檢視問答