WHCSRL 技术网

MYSQL锁详解

在这里插入图片描述

一、共享锁(S锁)&&排它锁(X锁)

S锁:一个事务获取到S锁后,其他线程也可以获取数据的S锁,但是不能获取X锁。
X锁:一个事务获取到X锁后,其他事务无法获取S锁,也无法获取X锁。
增删改查的加锁情况:
delete:加X锁
insert:一般情况不加锁
update:不更新ID值,则加X锁。 更新ID值,相当于做了delete和insert操作。
select:普通的select语句不加锁,select … lock in share mode可以加S锁,select for update加X锁

注意:
数据加上X锁后,再不能给数据加S锁和X锁,但是,依然可以对数据进行普通的select查询,而对数据进行增删改和加锁的select会被锁住。
当开启事务后,当事务提交或回滚,锁才会释放,而不开启事务,自动提交时,执行完语句锁就会释放。

二、行锁&&表锁

首先需要注意的是,行锁可以是S锁,也可以是X锁,同理,表锁也可以是S锁或X锁。行表锁和XS锁是两个维度来描述表的,他们并不独立。
在innodb中,当sql语句命中索引(主键索引/辅助索引)时,才会为命中的数据加行锁,否则,sql语句要进行全表扫描,就对命中数据加表锁了。
我们以update加锁为例,进行讲解。上面讲到,update加的是X锁,看如下语句:
在这里插入图片描述
这个会话中,update的X锁会一直锁着这条数据。我们重新打开一个会话,执行下面语句,看效果:
在这里插入图片描述可以看出,这条数据已经被锁住,我们加个共享锁看效果:
在这里插入图片描述
可见,S锁对这条数据也会被锁。因为它已经被X锁锁住了。
我们用普通的select语句却可以查询出结果,如下图:
在这里插入图片描述
因为没有给这条语句再加锁,所以可以查询出。我们查id为3的数据,看结果:
在这里插入图片描述
也可以查询出结果,因为是行锁,其他行数据并不受影响。

下面让其命中表锁,看效果:
在这里插入图片描述
上图sql没有命中索引,会锁表,我们打开一个新会话,看效果:
在这里插入图片描述
在这里插入图片描述
可见,锁了全表,操作不出任何数据了。

间隙锁
间隙锁是行锁的一种,所以,sql语句必须要命中索引,而且还要是范围查询,而不是等值查询,才会触发间隙锁。间隙锁锁住的是命中索引区间的范围数据,左开右闭,当再有数据插入这个区间或者修改这个区间的右侧数据时,会被锁住。
注意:间隙锁只会出现在可重复读的事务隔离级别中,mysql5.7默认就是可重复读。如果把隔离级别设置为不可重复读,则间隙锁不再存在。
下面看实验:
在这里插入图片描述
上面sql命中了主键索引,索引加行锁,而且是范围查询,命中数据是id为2,3,4的数据。
那么,间隙锁就是锁住了id为4和5的区间,而没锁住1和2的区间。我们来验证:
因为id为5的数据已经有了,我们无法再往4和5之间插入数据,因为id不能是4.5,我们删除id为5的数据,可以发现,删除不了:
在这里插入图片描述
虽然查询的数据没有命中id=5的数据,但是因为间隙锁,锁住了4和5区间,所以5是删不掉的。而id是1的值,是可以删除的。

三、乐观锁&&悲观锁

乐观锁和悲观锁是一种锁思想,用于控制并发访问,不是Mysql独有的。简单来说,乐观锁是借助程序来完成的,例如,比较版本号或者时间戳,来判断数据是否被其他线程占用过。悲观锁是利用数据库提供的锁机制来完成的,例如,利用mysql的排他锁,来实现悲观锁的思想。所以,乐观锁和悲观锁是一种操作方式,而不是mysql提供的锁机制。详情可参考:乐观锁vs悲观锁

推荐阅读