innoDB的8锁与不同SQL使用锁的策略

InnoDB的8锁

 

1、共享锁（X锁）与排它锁（S锁）

共享和排它是锁的模式，在不同锁的类型中都会有这两种模式！一般innoDB中我们所指的是行级的共享锁和排它锁，当然还有表级的共享锁和排它锁，意向锁也有共享与排它之分，即IS和IX。

兼容性上：

 

	X锁	S锁
X锁	不兼容	不兼容
S锁	不兼容	兼容

加锁与解锁：

 

	X/S	加锁	解锁
表级	S锁	Lock table…read	Unlock tables
	X锁	Lock table…write
行级	S锁	Select…for share/lock in share mode	事务commit/rollback自动解锁
	X锁	Select…for update update/delete/insert自动加X锁

注：普通的select（快照读）是不加锁，也不会以上的X/S锁阻塞，属于一致性的非锁定读，底层采用的MVCC，实现读写并发！

 

2、意向锁

• 意向锁主要是用来解决行表锁的冲突问题，实现多粒度的并发控制。
• 想象这么一个情况：事务A获得行1的X锁，事务B获得整表的X锁，理论上事务B是可以修改表中的任何一行包含行1，但是这是不符合事务隔离性的。
• 我们有两种办法来解决这个问题：
• 1：在事务A获得X锁后，在表上加上一个意向排它锁，表名有事务在处理该表的某些行，不允许其他事务再获得表级的锁。
• 2：进行表扫描，确定该表是否有行被锁定，然后确定是否加表级所。
• 显然第一种的效率要更高，而innoDB采用的就是表级的意向锁！
• LOCK_MODE:IS和IX

 

意向共享锁（IS）：事务在获得行级的S锁之前，会首先获得表上的意向共享锁！

意向排它锁（IX）：事务在获得行级的X锁之前，会首先获得表级的意向排它锁！

 

意向锁和表级锁的兼容性如下：

 

	S	X	IS	IX
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容
IS	兼容	不兼容	兼容	兼容
IX	不兼容	不兼容	兼容	兼容

注意：意向锁不和行级锁冲突！

 

3、索引记录锁（record locks）

• 所谓索引记录锁就是行锁，索引记录锁总是锁定索引记录，即使表中没有创建索引，innoDB会自动根据表中的隐藏的row_id字段创建隐藏的聚簇索引（GEN_CLUST_INDEX)!
• LOCK_MODE——S,REC_NOT_GAP和X,REC_NOT_GAP，即共享行锁和排它行锁！

 

4、间隙锁

• 间隙锁是对索引记录之间间隙的锁定，可以是第一个索引记录之前的间隙或者两个索引记录之间或者后一个记录之后的间隙！
• 间隙锁是可以共存的，即事务A获得某个间隙锁，事务B也可以同时获得同一个间隙锁，这就会导致死锁问题！
• LOCK_MODE:S,GAP和X,GAP，两者没有区别。不过在data_locks表中会在第一个不满足查询条件的记录上加上X.GAP。
• 间隙锁唯一的目的就是防止其他事务向gap中插入数据行，用来防止插入意向锁的。如果插入意向锁和间隙锁有重叠，则会阻塞插入，防止幻读！
• 注意：隔离级别是RC下，gaplock会被禁用，只有在外键约束检核和重复key检查是gaplock才会有效，因此RC才会有幻读问题！

 

5、临键锁（next -key-locks)

临键锁其实是索引记录锁和间隙锁的解锁，锁定的了该条索引记录和素银记录之前的间隙！

 

• 临键锁在官方文档说明中比没有说会所动索引后面的间隙，但是其实会在第一个不满足添加的索引上加间隙锁！例如如果有普通索引10，15，20，25，通过索引查询15的数据行时，会有一个临键锁（10，15]和一个间隙锁（15，20），来锁定（10，20）这个间隙！
• 当查询的索引含有唯一属性时，临键锁会降级为行锁（分为不同情况，第二部分会讨论）！
• 如果使用普通的辅助索引进行查询，则会在辅助索引上使用临键锁，主键索引采用行锁！
• LOCK_MODE：X和S

 

6、插入意向锁（insert intention locks）

一种特殊的gaplock！

• 在事务进行insert之前会先在索引记录之间间隙设置插入意向锁，该锁的范围是(插入值，下一个索引值），如果存在索引（4，7），事务A要插入5，则此时会先获取插入意向锁，其范围是（5，7）。
• 多个事务同一个index gap并发插入时，不会阻塞！比如（4，7）之插入5，6，会同时进行！
• 但是插入的间隙存在gap lock，即锁的范围重叠，则会阻塞插入，防止幻读！

 

7、自增锁(auto-inc locks)

表级锁，在插入语句结束后释放（注意不是事务结束！）

不常用，略！具体请参见官方文档！

 

8、空间索引！

不常用，略！具体请参见官方文档！

 

不同的SQL使用什么样的锁

 

1、加锁跟哪些因素有关

• 事务的隔离级别，RC下只有行级锁，没有gap锁（除了外键约束检核和重复key检查会使用gap锁）！
• SQL是一致性非锁定读（不加锁），还是一致性锁定读（加锁）！
• 是否使用索引和索引类型！

一些特殊情况：

• 一致性的非锁定读，没有使用锁，以下不讨论
• 没有使用索引或者索引失败的情况下，是全表加行锁+间隙锁！

2、RU隔离级别

读不加锁，写加排它锁！排它锁不会阻塞读，只会阻塞写，读总是能读到最新的记录，因此会有脏读问题！

 

3、serializable隔离级别

读加lock in share mode共享锁，不支持一致性的非锁定读！其他都加排它锁！

 

4、RR隔离级别

有这么一张表作为示例：

• 使用非唯一索引扫描或者搜索，则会在扫描每一个索引记录上设置的next-key lock！如果使用了辅助索引，在辅助索引上设置next-key lock在聚簇索引上创建行锁！
• 使用唯一索引，进行等值查找
  • 如果这个值存在会设置行锁，不锁定间隙；
  • 例如：select * from testlock where id=12 for update;

• 如果这个值不存在，或在第一个不满足条件的索引值上设置gap lock。
• 例如：select * from testlock where id=150 for update;

• 使用唯一索引，进行范围查找
  • 索引值存在：在第一个索引上加上行锁，扫描的每一个索引值上记上next-key lock；同时在第一个不满足条件的索引值上加上next-key lock。
  • 例如 select * from testlock where id>=12 and id<=101 for update;示例中锁定的范围是（12，222）

 

• 索引值不存在：在范围内每一个索引值加上next-key lock，并在第一个不满足条件的索引值上加上next-key lock。
• 例如： ;这个sql锁定的范围101的前一个索引即12到222，（12，2222）。

 

• Insert插入。
  • Insert插入时，会在插入索引值的下一个索引值上设置一个插入意向锁！
  • 锁定范围是（插入的索引值，插入索引的下一个索引值）
  • 如果和gap lock锁定的区间有重叠，则阻塞插入！
  • 例如：事务A： select * from testlock where id>=30 and id<=400 for update； 事务B：insert into testlock values(11,2012,'qwadse');阻塞！

 

5、RC隔离级别

• 只在满足条件的索引值上加上行锁
• 禁用了gap lock！

6、RR和RC

• 如果主键或者唯一索引存在重复建错误时，会在重复的索引值上设置shared next-key lock或shared index record lock！ 这样会导致死锁
• 例如，存在一个一个a,事务A插入a=1；事务B也插入a=1;事务C也插入a=1；事务A先执行获得排它行锁，B和C等待A解决，当A解锁之后B和C会同时获得S锁，但是由于B和C请求X锁，所以死锁产生了！