MYSQL 死锁

2023-01-08 15:58:25


mysql> select * from books;
+----+------------------------+-------+-------------+---------------------+
| Id | Title                  | Price | PublishDate | ModifyDate          |
+----+------------------------+-------+-------------+---------------------+
|  1 | Java编程思想           | 98.50 | 2005-01-02  | 2021-01-08 14:50:48 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09  | 2021-01-08 14:51:17 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23  | 2021-01-08 14:51:48 |
|  4 | C++编程思想            | 88.50 | 2004-01-09  | 2021-01-08 14:52:15 |
|  5 | HeadFirstJava          | 55.70 | 2013-12-17  | 2021-01-08 14:52:43 |
|  6 | 疯狂Android            | 19.50 | 2014-07-31  | 2021-01-08 14:53:27 |
+----+------------------------+-------+-------------+---------------------+
6 rows in set (0.00 sec)

SessionA:
mysql> delete from books where id=6;
Query OK, 1 row affected (0.06 sec)

SessionB:
mysql>  delete from books where id=1;
Query OK, 1 row affected (0.00 sec)

死锁比较简单理解,就是两个或者多个事务之间相互加锁,因为事务加锁算法是逐行加锁的. 这就导致你加了前面几行,我加了后面几行,你我相遇表中间这几行. 请问是你让我呢? 还是我让你? 不好意思事务是不讲武德的.

MYSQL RR隔离级别上 依旧主键 单语句,单行,外加上自动提交功能,基本不会遇到死锁. 死锁主要集中在多语句的事务组成和加了GAP锁


mysql> select * from books;
+----+------------------------+-------+-------------+---------------------+
| Id | Title                  | Price | PublishDate | ModifyDate          |
+----+------------------------+-------+-------------+---------------------+
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09  | 2021-01-08 14:51:17 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23  | 2021-01-08 14:51:48 |
|  4 | C++编程思想            | 88.50 | 2004-01-09  | 2021-01-08 14:52:15 |
|  5 | HeadFirstJava          | 55.70 | 2013-12-17  | 2021-01-08 14:52:43 |
+----+------------------------+-------+-------------+---------------------+
4 rows in set (0.00 sec)

添加回这两条记录

事务开启的时候 BEGIN; START TRANSACTION; 需要人工提交

SessionA:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM books WHERE id=2;
Query OK, 1 row affected (0.00 sec)

<==SessionB: 插入会话B 两个会话交叉执行
mysql> DELETE FROM books WHERE id=4;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=5;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

SessionB:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM books WHERE id=5;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=3;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=7;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=8;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=4;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM books WHERE id=2;
Query OK, 1 row affected (0.00 sec)

mysql>

会话A 加了记录锁 ID=(2,4) 欲加5

会话B 加了记录锁 ID=(5,3,7,8) 欲加4

死锁日志
SHOW ENGINE INNODB STATUS;


------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-08 15:21:01 0x7f726e9dd700
*** (1) TRANSACTION:
TRANSACTION 2094, ACTIVE 377 sec starting index read   <==SessionA:
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 16, OS thread handle 140129549526784, query id 452 localhost root updating
DELETE FROM books WHERE id=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2094 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0151; asc       Q;;
 3: len 13; hex 4865616446697273744a617661; asc HeadFirstJava;;
 4: len 5; hex 8000003746; asc    7F;;
 5: len 3; hex 8fbb91; asc    ;;
 6: len 4; hex 5ff8013b; asc _  ;;;

*** (2) TRANSACTION: 
TRANSACTION 2095, ACTIVE 172 sec starting index read    <==SessionB:
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4
MySQL thread id 17, OS thread handle 140129458837248, query id 453 localhost root updating
DELETE FROM books WHERE id=4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2095 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d01d8; asc        ;;
 3: len 13; hex e796afe78b82416e64726f6964; asc       Android;;
 4: len 5; hex 8000001332; asc     2;;
 5: len 3; hex 8fbcff; asc    ;;
 6: len 4; hex 5ff804d3; asc _   ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0190; asc        ;;
 3: len 22; hex e7acace4b880e8a18c416e64726f6964e4bba3e7a081; asc          Android      ;;
 4: len 5; hex 800000455a; asc    EZ;;
 5: len 3; hex 8fbed7; asc    ;;
 6: len 4; hex 5ff80104; asc _   ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0151; asc       Q;;
 3: len 13; hex 4865616446697273744a617661; asc HeadFirstJava;;
 4: len 5; hex 8000003746; asc    7F;;
 5: len 3; hex 8fbb91; asc    ;;
 6: len 4; hex 5ff8013b; asc _  ;;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0217; asc        ;;
 3: len 16; hex 4a617661e7bc96e7a88be6809de683b3; asc Java            ;;
 4: len 5; hex 8000006232; asc    b2;;
 5: len 3; hex 8faa22; asc   ";;
 6: len 4; hex 5ff804f4; asc _   ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2095 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000082e; asc      .;;
 2: len 7; hex 010000011c0198; asc        ;;
 3: len 15; hex 432b2be7bc96e7a88be6809de683b3; asc C++            ;;
 4: len 5; hex 8000005832; asc    X2;;
 5: len 3; hex 8fa829; asc   );;
 6: len 4; hex 5ff8011f; asc _   ;;

*** WE ROLL BACK TRANSACTION (1)

这个死锁日志 稍微细看下就能看得50%的明白,不像ORACLE样!

从头往下看


TRANSACTION 2094, ACTIVE 377 sec starting index read   <==SessionA:

这句说明 是事务2094 也就是我们的事务A


MySQL thread id 16, OS thread handle 140129549526784, query id 452 localhost root updating
DELETE FROM books WHERE id=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

这点信息是说 线程ID OS线程 QUERY ID 以及 语句

下面是 等什么样的锁

RECORD LOCKS 表示记录锁,space id为2,page号4 ，n bits 80表示这个聚集索引记录锁结构上留有80个Bit位


RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2094 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0151; asc       Q;;
 3: len 13; hex 4865616446697273744a617661; asc HeadFirstJava;;
 4: len 5; hex 8000003746; asc    7F;;
 5: len 3; hex 8fbb91; asc    ;;
 6: len 4; hex 5ff8013b; asc _  ;;;

其中这段就是行记录; 0 是聚集索引的值,1是事务ID; 2是UNDO地址;3TITLE;

4 价格; 5 发行日期 6 修改日期

LEN 表示字节长度; ASC 翻译成ASCII码 是啥? 

 0: len 4; hex 80000005; asc     ;;          ##主键
 1: len 6; hex 00000000082f; asc      /;; ##事务ID
 2: len 7; hex 020000011d0151; asc       Q;; ## UNDO地址
 3: len 13; hex 4865616446697273744a617661; asc HeadFirstJava;;
 4: len 5; hex 8000003746; asc    7F;;
 5: len 3; hex 8fbb91; asc    ;;
 6: len 4; hex 5ff8013b; asc _  ;;;

看下事务B 绿色是我人工写上去的. 会话B 锁了记录(7,3,5,8)


*** (2) TRANSACTION: 
TRANSACTION 2095, ACTIVE 172 sec starting index read    <==SessionB:
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4
MySQL thread id 17, OS thread handle 140129458837248, query id 453 localhost root updating
DELETE FROM books WHERE id=4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2095 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d01d8; asc        ;;
 3: len 13; hex e796afe78b82416e64726f6964; asc       Android;;
 4: len 5; hex 8000001332; asc     2;;
 5: len 3; hex 8fbcff; asc    ;;
 6: len 4; hex 5ff804d3; asc _   ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0190; asc        ;;
 3: len 22; hex e7acace4b880e8a18c416e64726f6964e4bba3e7a081; asc          Android      ;;
 4: len 5; hex 800000455a; asc    EZ;;
 5: len 3; hex 8fbed7; asc    ;;
 6: len 4; hex 5ff80104; asc _   ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0151; asc       Q;;
 3: len 13; hex 4865616446697273744a617661; asc HeadFirstJava;;
 4: len 5; hex 8000003746; asc    7F;;
 5: len 3; hex 8fbb91; asc    ;;
 6: len 4; hex 5ff8013b; asc _  ;;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 00000000082f; asc      /;;
 2: len 7; hex 020000011d0217; asc        ;;
 3: len 16; hex 4a617661e7bc96e7a88be6809de683b3; asc Java            ;;
 4: len 5; hex 8000006232; asc    b2;;
 5: len 3; hex 8faa22; asc   ";;
 6: len 4; hex 5ff804f4; asc _   ;;

欲要加锁 记录ID=4上 加记录锁X NOT GAP


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `bookstore`.`books` trx id 2095 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000082e; asc      .;;
 2: len 7; hex 010000011c0198; asc        ;;
 3: len 15; hex 432b2be7bc96e7a88be6809de683b3; asc C++            ;;
 4: len 5; hex 8000005832; asc    X2;;
 5: len 3; hex 8fa829; asc   );;
 6: len 4; hex 5ff8011f; asc _   ;;

死锁日志不会显示事务A的持有什么锁! 那么我们要用8.0的DATA_LOCKS

mysql> SELECT OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
    -> FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL          | TABLE     | IX                      | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+-------------+------------+-----------+---------------+-------------+-----------+

删除不存在的数据造成的死锁

SessionA:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM books WHERE id=2;
<<==SessionB:start hear
mysql> INSERT INTO books values(2,'HeadFirst设计模式',55.70, date('2010-11-09'));

SessionB:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM books WHERE id=4;
Query OK, 1 row affected (0.00 sec)

INSERT INTO books values(4,'C++编程思想',88.70, date('2004-01-09'));

假设表有记录1，5

SESSIONA ：ID=2不存在 那么会加锁GAP X （1，5）范围

SESSIONB ：ID=4不存在 那么会加锁GAP X （1，5）范围

INSERT ID=2 会发生锁等待 sessionB 释放锁 记录5

INSERT ID=4 会发生锁等待 sessionA 释放锁 记录5

RR隔离下GAP 加锁范围比较大 （1，5） （X,5) （5，+00）

插入意向死锁

mysql> CREATE TABLE T (A INT UNSIGNED NOT NULL PRIMARY KEY ,B INT);
mysql> INSERT INTO T VALUES(10,0),(20,0);

SessionA:
mysql> BEGIN;
mysql> UPDATE T SET B=1 WHERE A=20;
<<=SESSION BEGIN; SELECT * FROM T LOCK IN SHARE MODE;
mysql> INSERT INTO T VALUES(11,1);

SESSION B:
mysql>  BEGIN;
mysql>  SELECT * FROM T LOCK IN SHARE MODE;

<<==当SESSIONA 执行INSERT INTO T VALUES(11,1)；
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


mysql> SELECT OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+---------------+-------------+-----------+
| T           | NULL          | TABLE     | IX                       | GRANTED     | NULL      |
| T           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20        |
| T           | NULL          | TABLE     | IS                     | GRANTED     | NULL      |
| T           | PRIMARY    | RECORD    | S                      | GRANTED     | 10        |
| T           | PRIMARY    | RECORD    | S                      | WAITING     | 20        |
+-------------+------------+-----------+---------------+-------------+-----------+

从这里看出 SESSION A 第一个UPDATE语句 IX + X 记录

SESSION B 共享锁 锁定 ID=(10，20 ) NEXT-KEY LOCK (S)

SESSION A 阻塞了SESSION B

SESSION A 执行插入语句后 立马SESSION B死锁你看 OVER后


mysql> SELECT OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
+-------------+------------+-----------+------------------------+-------------+-----------+
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA |
+-------------+------------+-----------+------------------------+-------------+-----------+
| T           | NULL          | TABLE     | IX                                       | GRANTED     | NULL      |
| T           | PRIMARY    | RECORD    | X,REC_NOT_GAP                  | GRANTED     | 20        |
| T           | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | GRANTED     | 20        |
+-------------+------------+-----------+------------------------+-------------+-----------+
看到 INSERT INTO T VALUES(11,1)； X,GAP,INSERT_INTENTION
----------+------------+-----------+---------------+-------------+-----------+

死锁日志


------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-08 16:15:55 0x7f727405a700
*** (1) TRANSACTION:
TRANSACTION 421605069725968, ACTIVE 35 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 17, OS thread handle 140129458837248, query id 486 localhost root executing
SELECT * FROM T LOCK IN SHARE MODE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `bookstore`.`T` trx id 421605069725968 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000014; asc     ;;
 1: len 6; hex 00000000084f; asc      O;;
 2: len 7; hex 01000001230151; asc     # Q;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2127, ACTIVE 90 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140129549526784, query id 488 localhost root update
INSERT INTO T VALUES(11,1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `bookstore`.`T` trx id 2127 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000014; asc     ;;
 1: len 6; hex 00000000084f; asc      O;;
 2: len 7; hex 01000001230151; asc     # Q;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `bookstore`.`T` trx id 2127 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000014; asc     ;;
 1: len 6; hex 00000000084f; asc      O;;
 2: len 7; hex 01000001230151; asc     # Q;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

SEESION B 加锁范围 （-00，10],(10,20],[20,+00)

SEESION A                                 SEESION B
A=20 lock(x) 记录X           
                                                  A=10 LOCK (S) ;A=20 LOCK(S) waite SESSION A ； NEXTKEY LOCK(S)
INSERT INTO T VALUES(11,1);

MYSQL 死锁

这三个锁基本上都不兼容

其他锁兼容

MYSQL 死锁

锁模式：

MYSQL 死锁

选择UNDO资源少的回话被KILL掉

MYSQL 死锁

上一篇：PHP多维数组对汉字排序


下一篇：MySQL的一种数据库表死锁场景分析及规避方案