MYSQL 死锁

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 transactionmysql>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 1LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2MySQL thread id 16, OS thread handle 140129549526784, query id 452 localhost root updatingDELETE 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 waitingRecord 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 13 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4MySQL thread id 17, OS thread handle 140129458837248, query id 453 localhost root updatingDELETE 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 gapRecord 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 waitingRecord 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 updatingDELETE 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 waitingRecord 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 13 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4MySQL thread id 17, OS thread handle 140129458837248, query id 453 localhost root updatingDELETE 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 gapRecord 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 waitingRecord 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 hearmysql> 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 rowsmysql tables in use 1, locked 1LOCK 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 executingSELECT * 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 waitingRecord 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 insertingmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 16, OS thread handle 140129549526784, query id 488 localhost root updateINSERT 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 gapRecord 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 waitingRecord 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 BA=20 lock(x) 记录X                                                             A=10 LOCK (S) ;A=20 LOCK(S) waite SESSION A ； NEXTKEY LOCK(S)INSERT INTO T VALUES(11,1);

这三个锁基本上都不兼容

其他锁兼容

锁模式：

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

更多相关文章

随机推荐