MySQL死鎖案例 – Learn. Write. Repeat.
無論你是DBA還是開發人員，你都會對死鎖感到不耐煩，一些死鎖需要幾天的時間來修復，它們很難重現，其中一些只能在生產prod機器上重現。在不知道發生了什麼情況下盲目修復並不罕見，你只能假設問題出在哪裡，然後在這裡新增更多詳細日誌，最後建立一個補丁並將其投入生產，希望獲得更多資訊，這最近發生在我身上。在這篇文章中，我將嘗試分享關於如何處理mysql中的資料庫死鎖的。
拉一個mysql docker映象並啟動它：
docker pull mysql docker run --name local-mysql -e MYSQL_ROOT_PASSWORD=pass -d mysql:latest |
讓我們建立一個結構並導致死鎖。首先，我們需要連線到我們的mysql例項。獲取容器ID
docker ps -a |
然後連線：
docker exec -ti 94300e36a1d0 /bin/bash |
接下來連線到mysql例項:
mysql -p Enter password: pass |
我們建立一個資料庫：
CREATE SCHEMA test;
USE test;
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`child_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
`parent_name` varchar(45) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_child_fk_idx` (`child_id`),
CONSTRAINT `parent_child_fk` FOREIGN KEY (`child_id`) REFERENCES `child` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('1', 'child1');
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('2', 'child2');
INSERT INTO `test`.`child` (`id`, `child_name`) VALUES ('3', 'child3');
INSERT INTO `test`.`parent` (`id`, `parent_name`, `child_id`) VALUES ('1', 'parent1', '1');
|
現在我們已經插入了資料表結構和一些資料，我們就可以開始了。我們需要啟動兩個資料庫連線，以便我們可以產生死鎖。
第一個：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update child set child_name='child10' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
第二個：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into parent values(4,'parent2',1); |
我們回到第一個並寫入：
mysql> delete from parent; Query OK, 3 rows affected (0.00 sec) |
即看到第二階段出現死鎖：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
好，讓我們一步一步地進行調查。首先我們得到innodb引擎的狀態。
SHOW ENGINE INNODB STATUS; |
顯然我們對輸出的死鎖部分感興趣：
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-04-23 12:16:31 0x7fddcc0e7700 *** (1) TRANSACTION: TRANSACTION 2523, ACTIVE 7 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 109, OS thread handle 140590591276800, query id 374587 localhost root update insert into parent values(4,'parent2',1) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2523 lock mode S locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000000009d5; asc ;; 2: len 7; hex 010000012101ca; asc ! ;; 3: len 1; hex 61; asc a;; *** (2) TRANSACTION: TRANSACTION 2522, ACTIVE 9 sec fetching rows mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3 MySQL thread id 108, OS thread handle 140590588000000, query id 374588 localhost root updating delete from parent *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2522 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000000009d5; asc ;; 2: len 7; hex 010000012101ca; asc ! ;; 3: len 1; hex 61; asc a;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`parent` trx id 2522 lock_mode X waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 0000000009db; asc ;; 2: len 7; hex 81000000940110; asc ;; 3: len 1; hex 32; asc 2;; 4: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (1) |
我們看到事務一正在等待鎖被授權，有問題的鎖是在子表的主鍵上，它是一個共享（S）鎖。
insert into parent values(4,'parent2',1) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2523 lock mode S locks rec but not gap waiting |
再看到下一條資訊：
delete from parent *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 12 page no 4 n bits 72 index PRIMARY of table `test`.`child` trx id 2522 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13 page no 4 n bits 72 index PRIMARY of table `test`.`parent` trx id 2522 lock_mode X waiting |
第二段事務操作有兩個鎖。它在子表的主鍵上有一個獨佔（X）鎖，它等待父表的主鍵上的獨佔（X）鎖。這樣，第一段事務等待第二段事務釋放鎖，而第二段等待第一段釋放，相互等待，死鎖。
回顧我們的查詢，這非常有意義。首先我們這樣做的：
update child set child_name='child10' where id=1; |
這意味著它將在子表記錄上建立一個獨佔（X）鎖。我們可以透過執行驗證
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+-----------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+-----------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | +-------------+-----------+---------------+-----------+-------------+ 2 rows in set (0.00 sec) |
現在，當我們嘗試插入帶有child 1的新記錄：
insert into parent values(4,'parent2',1); |
我們被先前的獨佔（X）鎖定阻止。由於外來鍵約束， Insert需要子表記錄上的共享（S）鎖:
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+-----------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+-----------+-------------+ | parent | TABLE | IX | NULL | GRANTED | | child | TABLE | IS | NULL | GRANTED | | child | RECORD | S,REC_NOT_GAP | 1 | WAITING | | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | +-------------+-----------+---------------+-----------+-------------+ 5 rows in set (0.00 sec) |
當我們這樣做時，這是正常的:
mysql> delete from parent; |
等待一個排它（X）鎖的父表。但插入它還在等待共享（S）鎖。此時第二段事務被選為受害者（將被回滾），因此事務會話1獲勝。
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+------------------------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+------------------------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | | parent | TABLE | IX | NULL | GRANTED | | parent | RECORD | X | supremum pseudo-record | GRANTED | | parent | RECORD | X | 1 | GRANTED | | parent | RECORD | X | 2 | GRANTED | | parent | RECORD | X | 3 | GRANTED | +-------------+-----------+---------------+------------------------+-------------+ 7 rows in set (0.00 sec) |
所以，我們從這一切中學到了什麼。外來鍵和死鎖並不總是相互理解。插入/更新將透過其外來鍵使用共享鎖（S）鎖定子實體。這意味著只能讀取。
記錄鎖是對索引記錄的鎖定，即使定義了沒有索引的表，記錄鎖也始終鎖定索引記錄。對於這種情況，InnoDB建立一個隱藏的聚簇索引並使用此索引進行記錄鎖定。從MySQL 8.0.1開始，還有一些很好的功能，我將在下面介紹。
跳過鎖：
mysql> insert into parent select 4,'parent2',id from child where id=1 for update skip locked; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 |
上面修飾符可用於從表中非確定性地讀取行，同時跳過鎖定的行。我們看到我們沒有等待鎖，並且父程式沒有鎖定獨佔（X）鎖。 這是第二段事務。
mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+-----------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+-----------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,GAP | 2 | GRANTED | | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | +-------------+-----------+---------------+-----------+-------------+ 4 rows in set (0.00 sec) |
我們在第一段事務會話中進行刪除後得到：
mysql> delete from parent; Query OK, 3 rows affected (0.00 sec) mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+------------------------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+------------------------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,GAP | 2 | GRANTED | | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | | parent | TABLE | IX | NULL | GRANTED | | parent | RECORD | X | supremum pseudo-record | GRANTED | | parent | RECORD | X | 1 | GRANTED | | parent | RECORD | X | 2 | GRANTED | | parent | RECORD | X | 3 | GRANTED | +-------------+-----------+---------------+------------------------+-------------+ 9 rows in set (0.00 sec) |
很簡約。
此外，如果我們不想等待整個50秒（innodb_lock_wait_timeout）釋放鎖定，我們現在可以使用NOWAIT修飾符，它將立即引發錯誤。這是第二段事務會話。
mysql> insert into parent select 4,'parent2',id from child where id=1 for update nowait; ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+-----------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+-----------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | +-------------+-----------+---------------+-----------+-------------+ 3 rows in set (0.00 sec) |
在會話中刪除後我們得到了：
mysql> delete from parent; Query OK, 3 rows affected (0.00 sec) mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks; +-------------+-----------+---------------+------------------------+-------------+ | object_name | lock_type | lock_mode | lock_data | lock_status | +-------------+-----------+---------------+------------------------+-------------+ | child | TABLE | IX | NULL | GRANTED | | child | TABLE | IX | NULL | GRANTED | | child | RECORD | X,REC_NOT_GAP | 1 | GRANTED | | parent | TABLE | IX | NULL | GRANTED | | parent | RECORD | X | supremum pseudo-record | GRANTED | | parent | RECORD | X | 1 | GRANTED | | parent | RECORD | X | 2 | GRANTED | | parent | RECORD | X | 3 | GRANTED | +-------------+-----------+---------------+------------------------+-------------+ 8 rows in set (0.00 sec) |
再沒有死鎖！
相關文章
- 【MySQL】死鎖案例之六MySql
- 【MySQL】死鎖案例之七MySql
- 【MySQL】死鎖案例之八MySql
- 【MySQL】死鎖案例之四MySql
- 【MySQL】死鎖案例之一MySql
- 【MySQL】死鎖案例之二MySql
- 【MySQL】死鎖案例之三MySql
- MySQL批量更新死鎖案例分析MySql
- MySQL:Innodb 一個死鎖案例MySql
- 故障分析 | MySQL死鎖案例分析MySql
- MySQL死鎖案例一（回滾導致死鎖）MySql
- MySQL死鎖案例二（自增列導致死鎖）MySql
- 剖析6個MySQL死鎖案例的原因以及死鎖預防策略MySql
- 死鎖案例分析
- GreatSQL 死鎖案例分析SQL
- MySQL死鎖案例分析一(先delete，再insert，導致死鎖)MySqldelete
- MySQL 死鎖和鎖等待MySql
- 面試：什麼是死鎖，如何避免或解決死鎖；MySQL中的死鎖現象，MySQL死鎖如何解決面試MySql
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- MySQL 死鎖解決MySql
- MySQL死鎖問題MySql
- 【MySQL】漫談死鎖MySql
- MySQL解決死鎖MySql
- mysql行鎖和死鎖檢測MySql
- Mysql 兩階段鎖和死鎖MySql
- MySQL：一個死鎖分析 (未分析出來的死鎖)MySql
- MySQL 死鎖問題分析MySql
- MySQL：死鎖一例MySql
- Mysql如何處理死鎖MySql
- MySQL：MTS和mysqldump死鎖MySql
- MySQL列印死鎖日誌MySql
- MySQL 死鎖日誌分析MySql
- mysql死鎖最佳化MySql
- MySQL鎖等待與死鎖問題分析MySql
- MySQL InnoDB如何應付死鎖MySql
- MySQL強人“鎖”難《死磕MySQL系列 三》MySql
- MySQL死鎖系列-常見加鎖場景分析MySql
- MySQL：RR分析死鎖一列MySql