爱站程序员基地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 320: 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 320: 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 320: 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 320: 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 320: 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 320: 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 320: 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 /;; ##事务ID2: 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 320: 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 320: 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 320: 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 320: 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 320: 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 00: 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 00: 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 00: 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) 记录XA=10 LOCK (S) ;A=20 LOCK(S) waite SESSION A ； NEXTKEY LOCK(S)INSERT INTO T VALUES(11,1);
这三个锁基本上都不兼容
其他锁兼容
锁模式：
选择UNDO资源少的回话被KILL掉
