转载

【MySQL】InnoDB锁机制之二

一前言
之前的文章《InnoDB锁机制之一》介绍了InnoDB锁中的三种锁:record lock, gap lock,next-key lock ，本文继续介绍另外两种锁 Insert Intention Locks和AUTO-INC Locks
二常见的锁类型
2.1 根据锁持有的时间粒度，分为
1. 内存级别：类似mutex，很快释放
2. 语句级别：statement结束，释放
3. 事务级别：transaction提交或者回滚才释放
4. 会话级别：session级别，连接断开才释放

2.2 AUTO-INC lock
AUTO-INC lock是一个特殊的表级锁，当一个事务向含有自增字段的表插入数据时，该事务会获取一个AUTO-INC lock，其他事务必须等待直到已经获取锁的insert 语句结束。因此,多个并发事务不能同时获取同一个表上面的AUTO-INC lock,如果持有AUTO-INC锁太长时间可能会影响到数据库性能(比如INSERT INTO t1... SELECT ... FROM t2这类语句)或者死锁.
鉴于AUTO-INC 锁的特性,MySQL 5.1.22 通过新增参数 innodb_autoinc_lock_mode 来控制自增序列的算法。该参数可以设置为0,1,2.
在学习innodb_autoinc_lock_mode之前，我们先了解insert语句的类型
1 Simple inserts
能够事先确定具体行数的insert语句，比如 insert into tab values()...(); replace 等等。 INSERT ... ON DUPLICATE KEY UPDATE和还有子查询的insert 语句除外。
2 Bulk inserts
和Simple inserts对立，事先不能确定插入行数的 insert/replace语句，insert ... select ;replace ...select; load data into table .. 这种情况下Innodb在执行具体的行的时候会为每一行单独分配一个auto_increment 值。
3 Mixed-mode inserts
该情形是 Simple inserts 模式中，有些insert指定了自增字段的具体值，有些没有指定。比如:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
INSERT ... ON DUPLICATE KEY UPDATE

接下来我们再看MySQL对auto_increment 的优化模式。
innodb_autoinc_lock_mode=0，是传统的方式。InnoDB会在分配前给表加上AUTO_INC锁，并在SQL结束时释放掉。该模式保证了在STATEMENT复制模式下，备库执行类似INSERT … SELECT这样的语句时的一致性，因为这样的语句在执行时无法确定到底有多少条记录，只有在执行过程中不允许别的会话分配自增值，才能确保主备一致。
很显然这种锁模式非常影响并发插入的性能，但却保证了一条SQL内自增值分配的连续性。
innodb_autoinc_lock_mode=1 ，这个是InnoDB的默认值。该模式下对于Simple inserts，InnoDB会先加一个 autoinc_mutex锁，然后去判断表上是否有别的线程加了LOCK_AUTO_INC锁，如果有的话，释放autoinc_mutex，并使用传统的加锁模式。否则，在预留本次插入需要的自增值之后，就快速的将autoinc_mutex释放掉。很显然，对于普通的并发INSERT操作，都是无需加LOCK_AUTO_INC锁的。因此该模式提高了系统并发性；
innodb_autoinc_lock_mode=2，这种模式下只在分配时加个mutex即可，很快就释放，不会像值为1那样在某些场景下会退化到传统模式。因此设为2不能保证批量插入的复制安全性。

2.3 Insert Intention Locks
插入意向锁是gap 锁的一种，只是针对insert。当并发事务多条insert 插入同一个GAP,如果他们不是插入同一行记录，会话之间并不会相互等待。例如索引记录删有 12 ，17 两个记录，两个会话同时插入记录13，15，他们会分别为(12,17)加上GAP锁，但相互之间并不冲突（因为插入的记录不冲突）。
当向某个数据页中插入一条记录时，总是会调用函数lock_rec_insert_check_and_lock进行锁检查（构建索引时的数据插入除外），会去检查当前插入位置的下一条记录上是否存在锁对象，这里的下一条记录不是指的物理连续，而是按照逻辑顺序的下一条记录。
如果下一条记录上不存在锁对象：若记录是二级索引上的，先更新二级索引页上的最大事务ID为当前事务的ID；直接返回成功。
如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了GAP。如果GAP被锁住了，并判定和插入意向GAP锁冲突，当前操作就需要等待，加的锁类型为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，并进入等待状态。但是插入意向锁之间并不互斥。这意味着在同一个GAP里可能有多个申请插入意向锁的会话。

三参考文章
[1] 官方文档 innodb-auto-increment-handling
[2] MySQL · 引擎特性 · InnoDB 事务锁系统简介 --强烈推荐
[3] MySQL auto_increment实现

正文到此结束