SQL-mysql锁等待与死锁
一 前言
本篇是MYSQL高级进阶篇内容第二篇,学习本篇的基础是知识追寻者之前发布过的文章,尤其是《MYSQL架构入门篇》重中之重;
公众号:知识追寻者
知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)
二 锁等待
锁等待的意思非常好理解,就是session (事物会话,开启一个事物代表一个会话)A 对 某行数据获取独占锁(在这边一般就是写锁),然后session B 对相同的行进行获取独占锁就发生了锁等待;简单理解就是 小孩子抢玩具,谁先抢到 谁 先玩,没抢到的玩具的孩子只能 等待 抢到玩具孩子玩腻了再给你,瞬间泪奔有木有,就是这么残酷,当然MYSQL 没 这么残忍 其 还是有一个保留参数 innodb_lock_wait_timeout
指定死锁 的时间,如果超过 死锁等待时间就是报异常;
知识追寻者 做个实验:
session A 执行如下语句,开启事物,更新索引为1 的语句;此时 session A 获取了 id= 1 这条 语句的 写锁权限;
BEGIN;
update `order` set `year`= '2022' where id = '1';
session B 执行如下 语句 , 跟 上面的语句一样 ,由于 id =1 这条数据的写锁已经被session A 获取,故会发生锁等待的情况;
BEGIN;
update `order` set `year`= '2022' where id = '1';
知识追寻者这边默认等待了50秒 就报了如下异常
Lock wait timeout exceeded; try restarting transaction
查看 默认锁等待 语句
show VARIABLES like 'innodb_lock_wait_timeout'
三 死锁
3.1 死锁的产生
死锁 就是 两个以上的会话 在 抢占 资源过程中 ,产生相互等待的情况;有点绕是不是,其实很简单 死锁是建立在 锁等待的基础上,session A 获取 id = 1 的写锁 , session B 获取 id =2 的写锁 ,此时由于索引不同,故不会长生锁等待现象 ; 当 session A 尝试 获取 id =2 的 写锁时 ,由于 id = 2 写锁已经被 session B 获取 ,故产生锁等待;当 session B 尝试 获取 id = 1 的写锁时 ,由于id =1 写锁已经被 session A 获取, 此时 产生锁等待; 由于 session A 与 session B 同时 都在 锁 等待状态,产生了等待对方释放锁,故会产生死锁;
知识追寻者做个试验
session A 执行语句, 获取 id =1 的 写锁权限;
BEGIN;
update `order` set `year`= '2022' where id = '1';
session B 执行语句, 获取 id =2 的 写锁权限;
BEGIN;
update `order` set `year`= '2022' where id = '2';
session A 执行语句, 尝试获取 id =2 的 写锁权限,进入锁等待状态
update `order` set `year`= '2022' where id = '2';
session B 执行语句, 尝试获取 id =1 的 写锁权限,进入锁等待状态
update `order` set `year`= '2022' where id = '1';
当 B 进入 锁等待后就直接报死锁异常
Deadlock found when trying to get lock; try restarting transaction
3.2 查看死锁
可以使用 show engine innodb status
查看死锁
......
*** (1) TRANSACTION: // 事物A
TRANSACTION 253507, ACTIVE 474 sec starting index read
mysql tables in use 1, locked 1 // 已经使用一个锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ......
root updating
update `order` set `year`= '2022' where id = '2'//执行得语句
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253507 lock_mode X locks rec but not gap waiting
.....
*** (2) TRANSACTION: // 事物 B
TRANSACTION 253508, ACTIVE 425 sec starting index read
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 17002, OS thread handle 139824778569472, query id 2191735 ......
root updating
update `order` set `year`= '2022' where id = '1'//执行得语句
*** (2) HOLDS THE LOCK(S): //持有锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap
......
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 等待锁释放获取锁
RECORD LOCKS space id 65 page no 3 n bits 80 index PRIMARY of table `zszxz`.`order` trx id 253508 lock_mode X locks rec but not gap waiting
......
不得不说下字母代表锁得类型如下
- 共享锁(S)
- 排他锁(X)
- 意向共享(IS)
- 意向排他(IX)
- gap lock(GK), 间隙锁,锁定一个范围,不包括当前记录本身;
- RECORD LOCKS 代表记录锁;
可以看见上面得语句 (1) 代表 事物A ,MYSQL 线程id 17001 ; (2) 代表事物B, MYSQL 线程id 17002 ; 事物 A 与B 都在等待 对方释放锁 ,产生了死锁;
Tip; 查看表锁 : show status like 'table%';
如何解决死锁,知识追寻者这边给个思路:
查找到死锁线程,杀死MYSQL死锁的线程(kill命令);
如果事物未提交,直接回滚事物;
3.3 如何避免死锁
- 在死锁容易产生得表使用表锁不会产生死锁;
- 避免交叉使用相同的锁