Mysql

為什麼這個查詢會導致死鎖？

  • July 4, 2018

我在下面提供了原始 MySQL 查詢以及我以程式方式執行該操作的程式碼。如果同時執行兩個請求，則會導致以下錯誤模式：

SQLSTATE

$$ 40001 $$: 序列化失敗：1213 嘗試獲取鎖時發現死鎖；嘗試重新啟動事務 (SQL: update user_chats set updated_at = 2018-06-29 10:07:13 where id = 1)

如果我執行相同的查詢但周圍沒有事務塊，它將在許多並發呼叫中正常工作。為什麼 ？（交易獲取鎖，對吧？）

有沒有辦法在不鎖定整個表的情況下解決這個問題？（想盡量避免表級鎖）

我知道使用 InnoDB 在 MySql 中插入/更新/刪除表需要一個鎖，但仍然不明白為什麼會在這裡發生死鎖以及如何以最有效的方式解決它。

   START TRANSACTION;

   insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`)
       values (1, 2, 'dfasfdfk);
   update `user_chats`
       set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

   COMMIT;

以上是原始查詢，但我在 PHP Laravel Query Builder 中執行如下操作：

   /**
    * @param UserChatMessageEntity $message
    * @return int
    * @throws \Exception
    */
   public function insertChatMessage(UserChatMessageEntity $message) : int
   {
       $this->db->beginTransaction();
       try
       {
           $id = $this->db->table('user_chat_messages')->insertGetId([
                   'user_chat_id' => $message->getUserChatId(),
                   'from_user_id' => $message->getFromUserId(),
                   'content' => $message->getContent()
               ]
           );

           //TODO results in lock error if many messages are sent same time
           $this->db->table('user_chats')
               ->where('id', $message->getUserChatId())
               ->update(['updated_at' => date('Y-m-d H:i:s')]);

           $this->db->commit();
           return $id;
       }
       catch (\Exception $e)
       {
           $this->db->rollBack();
           throw  $e;
       }
   }

表的 DDL：

CREATE TABLE user_chat_messages
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user_chat_id INT(10) unsigned NOT NULL,
   from_user_id INT(10) unsigned NOT NULL,
   content VARCHAR(500) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id),
   CONSTRAINT user_chat_messages_from_user_id_foreign FOREIGN KEY (from_user_id) REFERENCES users (id)
);
CREATE INDEX user_chat_messages_from_user_id_index ON user_chat_messages (from_user_id);
CREATE INDEX user_chat_messages_user_chat_id_index ON user_chat_messages (user_chat_id);


CREATE TABLE user_chats
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

在這種情況下，外鍵user_chat_messages_user_chat_id_foreign是你死鎖的原因。

幸運的是，鑑於您提供的資訊，這很容易重現。

設置

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user_chat_id INT(10) unsigned NOT NULL,
   from_user_id INT(10) unsigned NOT NULL,
   content VARCHAR(500) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   CONSTRAINT user_chat_messages_user_chat_id_foreign FOREIGN KEY (user_chat_id) REFERENCES user_chats (id)
);

insert into user_chats (id,updated_at) values (1,NOW());

請注意，我刪除了user_chat_messages_from_user_id_foreign外鍵，因為它引用了users我們的範例中沒有的表。這對於重現問題並不重要。

重現死鎖

連接 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

連接 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

連接 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

此時，Connection 1 正在等待。

連接 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

在這裡，連接 2 引發了死鎖

ERROR 1213 (40001)：嘗試獲取鎖時發現死鎖；嘗試重啟事務

不使用外鍵重試

讓我們重複相同的步驟，但使用以下表結構。這次唯一的區別是刪除了user_chat_messages_user_chat_id_foreign外鍵。

CREATE DATABASE dba210949;
USE dba210949;

CREATE TABLE user_chats
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE user_chat_messages
(
   id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user_chat_id INT(10) unsigned NOT NULL,
   from_user_id INT(10) unsigned NOT NULL,
   content VARCHAR(500) NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

insert into user_chats (id,updated_at) values (1,NOW());

重現與以前相同的步驟

連接 1

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

連接 2

USE dba210949;
START TRANSACTION;
insert into `user_chat_messages` (`user_chat_id`, `from_user_id`, `content`) values (1, 2, 'dfasfdfk');

連接 1

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

此時，連接 1 執行，而不是像以前那樣等待。

連接 2

update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1;

連接 2 現在是正在等待的連接，但它還沒有死鎖。

連接 1

commit;

連接 2 現在停止等待並執行其命令。

連接 2

commit;

完成，沒有死鎖。

為什麼？

讓我們看看輸出SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-07-04 10:38:31 0x7fad84161700
*** (1) TRANSACTION:
TRANSACTION 42061, ACTIVE 55 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140383222380288, query id 81 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42061 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc     ;;
1: len 6; hex 00000000a44b; asc      K;;
2: len 7; hex b90000012d0110; asc     -  ;;
3: len 4; hex 5b3ca335; asc [< 5;;
4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) TRANSACTION:
TRANSACTION 42062, ACTIVE 46 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140383222109952, query id 82 localhost root updating
update `user_chats` set `updated_at` = '2018-06-28 08:33:14' where `id` = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc     ;;
1: len 6; hex 00000000a44b; asc      K;;
2: len 7; hex b90000012d0110; asc     -  ;;
3: len 4; hex 5b3ca335; asc [< 5;;
4: len 4; hex 5b3ca335; asc [< 5;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3711 page no 3 n bits 72 index PRIMARY of table `dba210949`.`user_chats` trx id 42062 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc     ;;
1: len 6; hex 00000000a44b; asc      K;;
2: len 7; hex b90000012d0110; asc     -  ;;
3: len 4; hex 5b3ca335; asc [< 5;;
4: len 4; hex 5b3ca335; asc [< 5;;

*** WE ROLL BACK TRANSACTION (2)

您可以看到事務 1在 的 PRIMARY 鍵上有一個lock_mode Xuser_chats，而事務 2 有lock_mode S，並且正在等待lock_mode X。這是它首先獲得共享鎖（來自我們的INSERT語句），然後是排他鎖（來自我們的UPDATE）的結果。

所以，發生的事情是連接 1 首先獲取共享鎖，然後連接 2 獲取同一記錄上的共享鎖。現在這很好，因為它們都是共享鎖。

然後連接 1 嘗試升級到獨占鎖來執行 UPDATE，卻發現連接 2 已經有了鎖。共享鎖和排他鎖不能很好地混合，正如您可能通過它們的名稱推斷出來的那樣。這就是為什麼它UPDATE在連接 1 上的命令之後等待的原因。

然後 Connection 2 嘗試UPDATE，這需要一個排他鎖，InnoDB 說“whelp，我永遠無法自己解決這種情況”，並聲明死鎖。它殺死連接 2，釋放連接 2 持有的共享鎖，並允許連接 1 正常完成。

解決方案

此時，您可能已經準備好停止 yap yap yap 並想要一個解決方案。以下是我的建議，按個人喜好排列。

1.完全避免更新

根本不用理會表格中的updated_at列user_chats。user_chat_messages相反，為列 ( user_chat_id, created_at)添加複合索引。

ALTER TABLE user_chat_messages
ADD INDEX `latest_message_for_user_chat` (`user_chat_id`,`created_at`)

然後，您可以通過以下查詢獲取最近更新的時間。

SELECT MAX(created_at) AS created_at FROM user_chat_messages WHERE user_chat_id = 1

由於索引，此查詢將非常快速地執行，並且不需要您也將最新updated_at時間儲存在user_chats表中。這有助於避免數據重複，這就是為什麼它是我的首選解決方案。

確保將 動態設置為該id值，而不是像我的範例中那樣$message->getUserChatId()硬編碼為。1

這基本上就是 Rick James 所建議的。

2.鎖表以序列化請求

SELECT id FROM user_chats WHERE id=1 FOR UPDATE

將此添加SELECT ... FOR UPDATE到事務的開頭，它將序列化您的請求。和以前一樣，確保將 動態設置id為該值，而不是像我的範例中那樣$message->getUserChatId()硬編碼為。1

這就是 Gerard H. Pille 的建議。

3.刪除外鍵

有時，消除死鎖的根源更容易。只需刪除user_chat_messages_user_chat_id_foreign外鍵，問題就解決了。

一般來說，我並不特別喜歡這種解決方案，因為我喜歡數據完整性（外鍵提供），但有時您需要做出權衡。

4.死鎖後重試命令

這是一般死鎖的推薦解決方案。只需擷取錯誤，然後重試整個請求。但是，如果您從一開始就做好準備，它最容易實現，而且更新遺留程式碼可能很困難。鑑於有更簡單的解決方案（如上面的 1 和 2），這就是我最不推薦針對您的情況的解決方案的原因。

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

相關問答

Mysql

（死鎖）選擇查詢導致插入查詢在事務中阻塞

  • August 5, 2020
檢視問答
Mysql

如何避免此查詢的死鎖？

  • April 16, 2019
檢視問答
Mysql

從多個事務更新相同的欄位

  • October 26, 2018
檢視問答
Mysql

MySQL：事務和死鎖

  • February 25, 2018
檢視問答
Mysql

InnoDB 中的 B-tree 是否使用標記刪除？

  • August 12, 2022
檢視問答
Mysql

MySQL InnoDB 遷移自定義實現：如何處理在後台觸發送出的 DML 語句？

  • June 21, 2022
檢視問答