技術文章 » MySQL鎖--07----insert 造成死鎖

MySQL鎖--07----insert 造成死鎖

2022-01-13 15:00:11

提示：文章寫完後，目錄可以自動生成，如何生成可參考右邊的幫助檔案

文章目錄

案例1----簡單insert

  • 簡單insert之間的死鎖

表結構

CREATE TABLE `emp1` (
   `id` int(16) NOT NULL,
   `name` varchar(16) DEFAULT NULL,
   `age` int(16) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

在這裡插入圖片描述
在這裡插入圖片描述

設定環境

  1. 常看當前資料庫的事務隔離級別： RR級別

SHOW VARIABLES LIKE ‘tx_isolation’;

在這裡插入圖片描述

  1. 設定: 手動提交事務

set autocommit = 0

  1. 開啟3個對談
    在這裡插入圖片描述

對談1，執行insert，不提交

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在這裡插入圖片描述

對談2，執行insert，由於id=4已被對談1上了行鎖，所以被堵塞

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在這裡插入圖片描述

對談3，執行insert，由於id=4已被對談1上了行鎖，所以被堵塞

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在這裡插入圖片描述

此時的事務鎖等待資訊：

SHOW VARIABLES LIKE ‘%innodb%’;

SET GLOBAL innodb_status_output_locks=ON;

在這裡插入圖片描述

SHOW ENGINE INNODB STATUS

在這裡插入圖片描述

繼續------對談1執行rollback

對談2，顯示insert成功

在這裡插入圖片描述

對談3，顯示出現死鎖

Deadlock found when trying to get lock; try restarting transaction

在這裡插入圖片描述

insert 插入前,對談需要先去拿S讀鎖,檢查唯一性

分析死鎖原因

  1. 對談1持有id=4的X鎖 寫鎖；
  2. 對談2插入前，需要拿S鎖檢查唯一性，但是id=4已經上了X鎖，所以等待；
  3. 對談3插入前，需要拿S鎖檢查唯一性，但是id=4已經上了X鎖，所以等待；
  4. 當對談1回滾，id=4上的X鎖釋放，那麼對談2和對談3同時獲得了S鎖，判斷唯一性；
  5. 由於對談2和對談3都拿著id=4的讀鎖，所以各自都無法在id=4上面獲取X鎖，死鎖出現。

有兩個需要注意的點：

  1. 只有拿到行鎖，才能檢查唯一約束，例如當對談1拿著X鎖時，對談2就沒辦法進行唯一約束判斷。
  2. 死鎖資訊中，或者show engine innodb status\G的事務資訊中，不一定會把事務持有鎖的情況都顯示，例如上面的死鎖資訊就沒有顯示第一個事務持有S鎖，但實際是有的；

那麼為什麼最後 會顯示對談2 插入成功了呢?

在這裡插入圖片描述

  1. Innodb檢測到死鎖之後,判斷對談3 事務較小,讓其回滾,釋放S鎖
  2. 此時對談2,拿到X寫鎖,插入成功

案例2----- on duplicate key update

表結構

mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> desc sam;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| col1  | int(11) | YES  | UNI | NULL    |       |
| col2  | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

對談1

  • 執行insert into sam values (4,3,3) on duplicate key update col1=4，不提交事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (4,3,3) on duplicate key update col1=4;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    4 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

對談2

  • 執行insert into sam values (4,4,3) on duplicate key update col1=5，被對談1堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (4,4,3) on duplicate key update col1=5;

對談3

  • 執行insert into sam values (5,4,3) on duplicate key update col1=5，被對談2堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (5,4,3) on duplicate key update col1=5;

此時的事務鎖等待資訊：

---TRANSACTION 69999, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 262 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70007 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------
---TRANSACTION 69998, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 260 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70006 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------

對談1執行rollback

  • 對談1執行rollback
  • 對談2，顯示insert成功
  • 對談3，顯示出現死鎖，
    事務被回滾ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖資訊：

LATEST DETECTED DEADLOCK
------------------------
2020-04-28 22:47:21 0x7f9ad57b8700
*** (1) TRANSACTION:
TRANSACTION 69998, ACTIVE 40 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 250 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69998 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 69999, ACTIVE 9 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 252 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

分析死鎖過程

  1. 對談1持有主鍵上id=3的X鎖，col1上唯一索引的id=3和id=4的X鎖；
  2. 對談2在等col1上唯一索引col1=4的next-key鎖；
  3. 對談3在等col1上唯一索引col1=4的next-key鎖；
  4. 對談1回滾，所有X鎖釋放；
  5. 本來對談2和對談3都要拿到col1唯一索引上(3,4]這一個next-key鎖，但對談1回滾後，col1=4就沒了，所以對談2和對談3的鎖就擴大了，變成(3，+∞]
  6. 對談2和對談3各自的next-key鎖堵塞了各自的insert操作

這裡也是需要注意兩點：

  1. 死鎖資訊同樣顯示不全，應該是兩個事務都持有lock_mode X，即(3,+∞]的next-key鎖，但死鎖資訊只顯示一個事務持有；
  2. insert into sam values (5,4,3) on duplicate key update col1=5，為什麼在insert之前，不需要在col1的唯一索引上先拿S鎖來檢測唯一性呢？這裡我猜由於這種insert語句比較特殊，本身就帶有on duplicate來處理唯一鍵衝突，所以直接拿X鎖在執行過程中來將唯一衝突處理掉。

案例3 ----- Replace into在這裡插入圖片描述