innodb锁机制,七种类型的锁,事务隔离级别

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

https://mp.weixin.qq.com/s/x_7E2R2i27Ci5O7kLQF0UA

https://mp.weixin.qq.com/s/wGOxro3uShp2q5w97azx5A