1 共享/排他锁(shared and exclusive Locks)
共享锁(Share Locks,记为S锁),读取数据时加S锁
排他锁(eXclusive Locks,记为X锁) 修改数据时加X锁
共享锁之间 不互斥,简单记为: 读读可以并行
排他锁与任何锁互斥,简单记为:写读,写写,不可以并行
共享/排它锁的潜在问题是,不能充分的并行,解决思路是数据多版本
2 意向锁(Intention Locks)
InnoDB支持多粒度锁,它允许行级锁与表级锁共存,实际应用中,innodb使用的时意向锁
意向锁时指,未来的某个时刻,事务可能要加共享/排它锁了,先提前声明一个意向
意向锁是一个表级别的锁;
意向共享锁(IS):它预示着,事务有意向对表中的某些行加共享S锁
意向排它锁(IX):它预示着,事务有意向对表中的某些行加排它X锁
例如 select ... lock in share mode 要设置IS锁
select ... from update 要设置 IX锁
意向锁主要是解决表锁和行锁的冲突的问题, 为了方便检测表级锁和行级锁的之间的冲突, 就引入了意向锁,表锁和行锁是互斥的关系,当前事务获取表锁,其他事务不能获取行锁,当前事务获取行锁,其他事务不能获取表锁
意向锁并不会锁住整张表,它只是一个意向,用来判断当前是否有锁表的动作,如果有会阻塞,如果没有,再判断是否可以获得行锁,不能获得则阻塞,但当前意向锁会阻塞其他互斥锁操作
3 记录锁(Record Locks)
记录锁,它封锁索引记录。例如 select * from t where id=1 for update
它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行
4 间隙锁 (Gap Locks)
间隙锁,它封锁索引记录中的间隙,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围
例如 select * from t where id between 8 and 15 for update;
会封锁区间 8到15,以阻止其他事务 在此区间 做 插入 更新操作
但是 我们如果把事务的隔离级别降级 为读提交(RC),间隙锁则会自动失效
5 临键锁(Next-key Locks)
临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间
依然是上面的例子,InnoDB,RR:
t(id PK, name KEY, sex, flag);
表中有四条记录:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
PK上潜在的临键锁为:
(-infinity, 1]
(1, 3]
(3, 5]
(5, 9]
(9, +infinity]
6 插入意向锁(Insert Intention Locks)
事务要获得 某些行的S锁,必须先获得表的IS锁,同理 x锁也一样,意向锁仅仅表明意向,它其实是比较锁的
锁,意向锁之间并不相互互斥,而是可以并行。但是它们会与共享锁/排它锁互斥
对于已有数据行的修改与删除,必须加强互斥锁X锁,那对于数据的插入,innodb使用的是插入意向锁,
是间隙锁的一种,也是实施在索引上的,它是专门针对insert操作。插入意向锁可以做到,多个事务,在同一
索引,同一个范围区间插入记录时,如果插入位置不冲突,不会阻塞彼此
加入 表中已经 有 3条数据 , 分别是10,20,30。我们在10,20这个区间,插入11,12。不会造成阻塞
7 自增锁(Auto-inc Locks)
在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,该实现方式,是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略
innodb_autoinc_lock_mode 可以设定3个值,0,1,2,
0 :traditional 通过表锁的方式进行,所有类型的insert 都用AUTO-inc locking
1 : consecutive默认值,产生一个轻量锁,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行
2 : interleaved 对所有的insert-like的自增长值的产生使用互斥量机制完成,并发性能最高,并发插入可能导致自增值不连续,可能会导致statement 和 replication 出现不一致,使用该模式,需要row replication 的模式
simple insert: 可以确定 具体要插入的行数 的插入
bulk insert :不能确定 具体要插入的行数的 插入 如load
自增的过程
第一种,插入空值的时候
当innodb_autoinc_lock_mode=0时
1 申请AUTO_INC锁
2 得到当前的AUTO_INCREMENT值n,并加1
3 执行插入操作,并将n写入新增的对应字段中
4 释放AUTO_INC锁
RBR, 基于行的复制
SBR, 基于语句的复制
在innodb_autoinc_lock_mode=2的时候,由于是来一个分配一个,故当replication模式为SBR(statement-based replication, SBR)的时候,如果发生Bulk inserts会在分配的时候向其他insert分配,就会出现主从不一致的情况,但是如果改为RBR就不会出现这种情况。
也就是说在RBR(row-based replication, RBR)模式下,innodb_autoinc_lock_mode=2是安全的,其他情况还是建议设置为1.
innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个,而不会锁表,只会锁住分配id的过程,和1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时存在问题
解决:
尽量让主键ID没有业务意义,或则使用simple inserts模式插入。
结论:
当innodb_autoinc_lock_mode为0时候, 自增id都会连续,但是会出现表锁的情况,解决该问题可以把innodb_autoinc_lock_mode 设置为1,甚至是2。会提高性能,但是会在一定的条件下导致自增id不连续。
总结:
通过上面2个问题的说明,自增主键会产生表锁,从而引发问题;自增主键有业务意义,不连续的主键导致主从主键不一致到出现问题。对于simple inserts 的插入类型,上面的问题都不会出现。对于Bulk inserts的插入类型,会出现上述的问题。
InnoDB 的事务隔离级别
InnoDB实现了 的 事务隔离级别分别是
读未提交 :select 不加锁,可能会出现脏读
读提交 RC:普通select快照读,锁select /update /delete 会使用记录锁,可能出现不可重复读;
可重复读 RR:普通select快照读,锁select /update /delete 根据查询条件情况,会选择记录锁,或者间隙锁/临键锁,以防止读取到幻影记录;
串行化:select隐式转化为select ... in share mode,会被update与delete互斥;
InnoDB默认的隔离级别是RR,用得最多的隔离级别是RC
四种事务隔离级别,实际上是一致性与并发性的一个权衡与折衷
各种SQL加了什么锁
1 普通select
在 读未提交 RU ,提提交 RC,可重复读RR 这三种事务隔离级别下,普通select 使用快照读,不加锁,并发非常高
在串行化这种事务的隔离级别下,普通select 会 升级成为 select ... in share mode
2 加锁 select
加锁 select 主要是指
select ... from update
select ... in share mode
如果,在唯一索引上使用 唯一的查询条件,会使用记录锁,而不会封锁记录之间的间隔,即不会使用间隙锁与临键锁
例如
select * from t where id=1 for update
只会封锁记录,而不会封锁区间
其他的查询条件和索引条件,innodb 会封锁被扫描的索引范围,并使用间隙锁与临键锁,避免索引范围区间插入记录
3 update 与 delete
和加锁select类似,如果 在唯一索引上使用唯一的查询条件来 update / delete , 也只加 记录锁
否则,符合查询条件的索引记录之前,都会加排他临键锁,来封锁索引记录与之前的区间
4 insert
同样 是写操作,insert 和 update 与 delete 不同,它会用排他锁封锁 被插入 的索引记录,而不会封锁
记录之前的范围
同时,会在插入区间加插入意向锁,但这个并不会真正封锁区间,也不会阻止相同区间的不同key插入
参考自
https://blog.csdn.net/qiuyepiaoling/article/details/49923703
https://mp.weixin.qq.com/s/iViStnwUyypwTkQHWDIR_w
https://mp.weixin.qq.com/s/y_f2qrZvZe_F4_HPnwVjOw