MYSQL 死锁
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL 死锁，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16797字，纯文字阅读大概需要24分钟。
内容图文
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);
这三个锁基本上都不兼容
其他锁兼容
锁模式：
选择UNDO资源少的回话被KILL掉
内容总结
以上是互联网集市为您收集整理的MYSQL 死锁全部内容，希望文章能够帮你解决MYSQL 死锁所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】