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.开启主动死锁检测,发现死锁后,主动回滚死锁链条中的某个事务,让其他事务得以执行