一、共享锁(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悲观锁
四、Mysql死锁详解
死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。在Innodb中,锁是逐步获得的,在Session A中,获取了id=1数据的锁,然后要获取id=2数据的锁,此时,Session B中先获取了id=2数据的锁,然后要获取id=1数据的锁,此时,Session A和Session B就出现了相互等待的局面,就是死锁。
示例:
如上图所示,session1和session2并发执行,且每个session都是逐步加锁,所以会第二条sql相互锁等待的情况。
参考文章:MySQL死锁产生原因和解决方法