一、概念介绍
mysql锁大类分为表锁和行锁,顾名思义表锁是把整张表锁住,行锁粒度小一点,对行进行加锁。同时,行锁是针对索引列,有索引,才会有行锁;注意,即使你没有创建主键索引,mysql会创建一个隐藏的主键索引列。
为什么要了解锁呢?
因为你需要当数据库死锁发生,事务阻塞,你需要知道是怎样造成的,才能解决。
二、行锁分类
1)共享锁shared (S
) lock
在一个事务里查询会持有共享锁
2)排他锁exclusive (X
) lock
在一个事务里删除或者修改会持有排他锁
3)间隙锁gap lock
在事务级别为REPEATABLE-READ级别,才有间隙锁;间隙锁可以避免幻读。注:如果加锁列是唯一索引列或者主键列,间隙锁会蜕化为记录锁,只锁一行。
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child VALUE(100,102);
数据100,102之间就是间隙,间隙加锁就是间隙锁;加锁区间前开后开,如(100,102),不包括100,102
SESSION A
begin;
SELECT * FROM child WHERE between 100 AND 102 FOR UPDATE; 执行成功,给100和102之间的间隙加锁;
SESSION B
INSERT INTO child VALUE(101);//block
SESSION A先执行,SESSION B会被阻塞,因为100和102之间的间隙被加锁了
4)Record Lock记录锁
记录锁就是把一行锁
5)Next-Key Lock
Next-Key Lock就是Record Lock+gap lock,加锁前开后闭,如(100,102]
6)Insert Intention Lock
插入意向锁,是一个特殊的gap lock,只会锁住插入那一行;在执行insert操作会先获取Insert Intention Lock;
注:插入意向锁不会跟任何其他锁互斥,除了表锁和全局锁;
三、表锁分类
1)意向锁
意向锁分为意向共享锁(IS)intention shared lock和意向排他锁(IX)intention exclusive lock
意向共享锁在获取共享锁之前会获取意向排他锁;意向排他锁在获取排他锁之前会获取意向排他锁
意向锁Intention Locks就是申明事务获取哪种类型的锁
SELECT ... LOCK IN SHARE MODE获取IS锁,SELECT ... FOR UPDATE获取IX锁,LOCK TABLES ... WRITE获取X锁,LOCK TABLES ... READ获取S锁,注:使用LOCK加锁,必须用UNLOCK释放锁或者客户端断开连接释放
下图是意向锁之间和共享锁和排他锁之间的互斥关系
X | IX | S | IS | |
X | Conflict | Conflict | Conflict | Conflict |
IX | Conflict | Compatible | Conflict | Compatible |
S | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
2)metadata lock
访问的时候会自动加上,就是表加上metadata lock锁,DDL操作会被阻塞;注:MDL读锁跟表级写锁互斥,跟表级读锁不互斥
metadata lock简称MDL锁,增删改查加的MDL读锁,DDL操作加的是MDL写锁,MDL读锁不互斥,MDL写锁、读写锁之间互斥.
注:metadata lock优先级最高,如果事务A得到MDL锁,事务B要获取MDL会被阻塞,事务C获取意向锁,阻塞;当事务A锁释放,锁会优先被分配给事务B.
四、总结
1.插入、删除和修改加的锁类型都是排他锁,删除和修改是加的是next-key的排他锁,插入是在索引上加一个记录排他锁。注:当插入发生a duplicate-key error,会设置一个共享锁。
2.行锁是在作用在索引上,如果一个表没有索引和主键,mysql会创建一个隐藏的主键列;
3.在可重复读级别才有间隙锁和next key lock
4.事务一提交,锁就会释放
5.如果加锁匹配不上任何索引,会对整个表加锁
6.加锁的基本单位为next-key lock,除了Insert操作
参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html,https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html,https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html