MySQL锁
文章目录
MySQL中锁的分类
- 全局锁：主要用于全库逻辑备份
- 表级锁：表锁、元数据锁、意向锁
表锁：通过lock tables语句对表进行加锁，它不仅限制其他线程的读写，也限制本线程接下来的读写
元数据锁：当我们对数据库进行操作时，会自动给这个表加上MDL，对一张表进行CRUD操作时，加的是MDL读锁；对一张表做结构变更时，加的是MDL写锁。MDL是为了保证用户对表执行CRUD操作时，防止其他线程对这个表结构做了变更
意向锁：当执行插入、更新、删除操作时，需要对表先加上 意向独占锁，然后对该记录加独占锁。意向锁的目的是为了快速判断表中是否有记录被加锁。
- 行级锁：记录锁、间隙锁、Next-Key Lock、插入意向锁(InnoDB支持行级锁、MyISAM不支持)
记录锁：锁住的是一条记录。而且记录锁是有S和X锁之分的，满足读写互斥，写写互斥。
间隙锁：只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下的幻读的现象。
Next-Key Lock：被称为临键锁，是记录锁+间隙锁的组合，锁定一个范围，并且锁定记录本身。
插入意向锁：当插入位置的下一条记录有间隙锁，那么就会自动生成插入意向锁，然后进入阻塞状态。
创建索引时会锁表吗
对表结构修改、增加字段、删除字段、增加索引、删除索引、更改索引名字、更换字段名字等等，这些操作都会加元数据写锁，都会与元数据读锁发生冲突
线上修改表结构会加什么锁
线上环境中，存在很多事务都在读这张表，如果此时对表结构进行修改，就会发生阻塞。因为有事务对这个表进行读写操作时，会生成元数据读锁，而在修改表结构时，会生成元数据写锁。这时候就产生了读写冲突，所以修改表结构的操作就会被阻塞，并且后续事务的增删改操作都会阻塞。
Innodb存储引擎的行级锁有哪些
1.记录锁
Record Lock称为记录锁，锁的是一条记录。
//对记录加共享锁
select ... lock in share mode;
//对记录加独占锁
select ... for update;
记录锁是有S锁和X锁之分的：
S锁和S锁之间兼容，S锁和X锁之间不兼容
当一个事务对一个记录加了S型记录锁后，其他事务也可以继续对该记录加S型记录锁；
但是其他事务不能对该记录加X型记录锁。
S锁和X锁之间不兼容，X锁和X锁之间也不兼容
当一个事务对一个记录加了X型锁后，其他事务既不能加S型锁，也不能加X型锁
比如：
一个事务对一个记录加一个X型记录锁，其他事务对这条记录就无法修改了
mysql > begin;
mysql > select * from tablename where id=1 for update;
这条事务commit后，事务过程中生成的锁都会被释放。
2.间隙锁
Gap Lock 被称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下的幻读的现象。
假设，表中有一个id范围为(3，5)的间隙锁，那么其他事务就无法插入id=4的这条记录了。也就是在李四和王五之间的id=4的位置，无法插入记录。
有效的防止了幻读现象的发生。
| id | 1 | 3 | 5 | 7 |
|---|---|---|---|---|
| name | 张三 | 李四 | 王五 | 赵六 |
间隙锁也分为S型间隙锁和X型间隙锁，但是间隙锁之间是兼容的。两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。
3.临键锁
Next-Key Lock被称为临键锁，是Record Lock + Gap Lock的组合，锁定一个范围，并且锁定记录本身。
假设，表中有一个id范围为(3，5]的临键锁，那么其他事务就无法插入id=4的这条记录了，并且无法修改id=5的这条记录了。也就是在李四和王五之间的id=4的位置，无法插入记录；并且id=5这条记录无法修改。
所以临键锁既可以保护该记录，也可以阻止其他事务把新记录插入到被保护记录前面的间隙中。
临键锁是Record Lock + Gap Lock的组合，如果一个事务获取了X型的next-key lock，那么其他事务在获取相同范围的X型的next-key lock时，是会被阻塞的。
比如，一个事务持有了范围为(1,10]的X型临键锁，那么另一个事务获取相同范围的临键锁会被阻塞。
虽然相同范围的间隙锁是多个事务相兼容的，但是对于记录锁，我们需要考虑X型和S型的关系。
4.插入意向锁
一个事务在插入一条记录时，需要判断插入位置是否已经被其他事务加了间隙锁(临键锁也有间隙锁)。
如果有，插入操作就会被阻塞，直到拥有间隙锁的那个事务提交为止，在此期间会产生一个插入意向锁，表明有事务想在某个区域插入新记录，但是现在处于等待状态。
比如，上面的例子。
事务1对id(3，5)之间插入一个间隙锁，事务2想对该表插入一个id=4的新记录。此时判断插入的位置被事务1加了间隙锁，于是事务2会生成一个插入意向锁，然后把锁的状态设置为等待，此时事务2就发生了阻塞，直到事务1提交了事务。
MySQL中加锁时，是先生成锁结构，然后设置锁状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有锁状态是正常状态时，才代表事务成功获取到了锁。
虽然插入意向锁，名字中有意向锁，但是它并不是意向锁，而是一个特殊的间隙锁，是行级锁。如果说间隙锁锁的是一个区间，那么插入意向锁锁的就是一个点。
它们之间的区别就是，尽管它们都是"间隙锁"，但是两个事务却不能同时，一个拥有间隙锁，另一个拥有该区间内的插入意向锁。
Update语句中，不带where条件，加什么锁？
InnoDB加锁是索引加锁，在可重复读隔离级别下一般加Next-Key Lock；在读已提交隔离级别下一般加记录锁。
- 更新时，如果不带where条件，就会发生全表扫描，对每个记录加锁：
可重复读隔离级别：发生全表扫描，对每个记录都加Next-Key Lock，相当于锁住全表
读已提交隔离级别：发生全表扫描，并且该隔离级别下没有间隙锁，对每条记录加记录锁
如果带了where条件，没有命中索引加什么锁？
同样的，没有命中索引会发生全表扫描。
可重复读隔离级别：发生全表扫描，对每个记录都加Next-Key Lock
读已提交隔离级别：发生全表扫描，并且该隔离级别下没有间隙锁，对每条记录加记录锁
MySQL实现乐观锁
基于版本号来实现乐观锁，修改数据时带上版本号即可。也可以使用时间戳
UPDATE student SET name='张三',version=2 WHERE id=1 AND version=1
在每次更新数据时，都带上版本号，同时将版本号加1，比如更新id=1，版本号为2的记录，先获取id为1的版本号，然后更新语句写成：
UPDATE student SET name='李四',version=version+1 WHERE id=1 AND version=2
如果这个版本号和记录中的一致(为2)，那就能更新成功；如果不一致，那就不进行更新，然后需要重新获取该记录的最新版本号，然后再尝试更新数据。
MySQL死锁
MySQL死锁是怎么发生的？
在并发事务中，两个事务出现循环资源依赖，这两个事务都在等待别的事务释放资源时，就导致了这两个事务都进入无限的等待的状态，此时就发生了死锁。
检查死锁
在遇上线上死锁问题时，我们应该第一时间获取相关的死锁日志，比如show engine innodb status命令获取死锁信息。
然后分析死锁日志，死锁日志分为两部分，上部分说明了事务1在等待什么锁，下半部分说明了事务2当前持有的锁和等待的锁。
通过阅读死锁日志，我们可以清晰的知道两个事务形成了怎样的循环等待，然后根据当前各个事务执行的SQL分析出加锁类型以及顺序，逆向推断出如何形成的循环等待，这样就能找到死锁产生的原因了。
示例：
| 步骤 | 事务1 | 事务2 |
|---|---|---|
| 1 | begin | |
| 2 | delete from test where a=2; | |
| 3 | begin | |
| 4 | delete from test where a=2;(事务1卡住) | |
| 5 | 提示死锁：ERROR 1213(4001):DeadLock found when | insert into test(id,a) values(10,2); |
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)
感兴趣可以去查看排查死锁
如何避免死锁
实际上，死锁是不能完全避免的，只要会加锁，在并发的场景就会发生死锁，但是我们可以通过一些手段，降低发生死锁的概率
- 缩短锁持久的时间，来降低死锁的概率
1.如果事务中需要锁多个行，那就把最有可能造成锁冲突的锁的申请时机尽量往后放，这样事务持有锁的时间就会比较短
2.避免大事务，尽量把大事务拆成多个小事务来处理，因为大事务占用耗时长，占用锁的时间较长，与其他事务冲突的概率会变高
- 减少间隙锁，来降低死锁的概率
如果能确定幻读和不可重复读对应用的影响不大，可以考虑把隔离级别改成RC(读已提交)，因为RC隔离级别没有间隙锁，可以避免间隙锁导致的死锁
- 减少加锁范围，来降低死锁的概率
给表添加合理的索引，如果不走索引将会为表的每一行记录加行级锁，死锁的概率会大大增加
- 通过MySQL参数设置，来降低死锁的概率：设置锁等待超时参数innodb_lock_wait_timeout；开启主动死锁检测innodb_deadlock_detect
1.设置合理的锁等待超时阈值，当一个事务的等待时间超过该值后，就对这个事务进行回滚，锁就会被释放
2.开启主动死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以执行
8322
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
