mysql-insert锁机制

注：不同的参数设置，不同的mysql版本，不同的事务隔离级别都对加锁过程有影响

1.通过show variables like 'innodb_locks_unsafe_for_binlog'可以查看gap锁是否开启

该参数默认值为0，即启用gap锁，但是这个参数的设置不会影响外键和唯一索引（包含主键）对gap进行加锁的需要。

2.通过show variables like 'innodb_autoinc_lock_mode'查看自增锁的模式

该参数默认有3种配置模式：

0（传统模式）：对auto_increment列插入语句时加表级auto-inc锁，只有插入执行结束之后才会释放锁

1（连续模式）：可以事先确定插入行数的语句（包括多行和单行），会分配连续确定的auto-increment值；对于插入行数不确定的语句，仍然加表级auto-inc锁。（注：在这种模式下，如果回滚，auto-increment并不会回滚，则导致自增列不连续）

2（交错模式）：同一时刻多条SQL语句产生交错的auto-increment值

行锁：

record lock：,仅仅锁住索引记录的一行，单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身

gap lock：仅仅锁住一个索引区间（开区间），在索引记录之间的间隙中加锁，或在某一条索引记录之前或之后加锁，并不包括索引记录本身

next-key锁：record lock+gap lock 左开右闭区间

插入意向锁（insert intention lock，属于gap lock）：在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，不需要等待其他事务完成。

加锁过程：insert会在插入成功的行加一个X锁，这个锁是record lock而不是一个next-key lock（更不会是gap lock），不会阻止其他并发事务往这条记录之前的间隙插入记录。在插入之前，会先在插入记录所在的间隙加上一个insert intention gap lock，并发事务可以对同一个加insert意向锁。如果insert的事务出现了主键重复错误，事务会对这个记录加共享锁。这个共享锁在并发的时候是会产生死锁。

行锁之间的兼容情况（横向为已加的锁）

说明：

insert操作之间不会有冲突

gap、next-key会阻止insert

gap和record、next-key不会冲突

record和record、next-key之间相互冲突

已有的insert锁不阻止任何准备加的锁

insert死锁场景分析

1.主键错误引发的死锁

表结构:

CREATE TABLE `aa` (

 `id` int(10) unsigned NOT NULL COMMENT '主键',

 `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',

 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

 `stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',

 PRIMARY KEY (`id`),

 UNIQUE KEY `udx_name` (`name`),

 KEY `idx_stage` (`stage`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据：

mysql> select * from aa;

+----+------+-----+-------+

| id | name | age | stage |

+----+------+-----+-------+

| 1 | yst | 11 |   8 |

| 2 | dxj |  7 |   4 |

| 3 | lb  | 13 |   7 |

| 4 | zsq |  5 |   7 |

| 5 | lxr | 13 |   4 |

+----+------+-----+-------+

注：如果T1未rollback，而是commit的话，T2和T3会报唯一键冲突：ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’

死锁原因：首先session1插入一条记录，获得该记录的排它锁，这时session2和session3都检测到了主键冲突错误，但是由于session1并没有提交，所以session1并不算插入成功，于是它并不能直接报错吧，于是session2和session3都申请了该记录的共享锁，这时还没获取到共享锁，处于等待队列中。这时session1 rollback了，也就释放了该行记录的排它锁，那么session2和session3都获取了该行上的共享锁。而session2和session3想要插入记录，必须获取排它锁，但由于他们自己都拥有了共享锁，于是永远无法获取到排它锁，于是死锁就发生了。如果这时session1是commit而不是rollback的话，那么session2和session3都直接报错主键冲突错误。

2.gap和insert intention冲突引发的死锁

表结构：

CREATE TABLE `t` (

 `a` int(11) NOT NULL,

 `b` int(11) DEFAULT NULL,

 PRIMARY KEY (`a`),

 KEY `idx_b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据：

mysql> select * from t;

+----+------+

| a | b  |

+----+------+

| 1 |  2 |

| 2 |  3 |

| 3 |  4 |

| 11 |  22 |

+----+------+

死锁原因：

事务T1执行查询语句，在索引b=6上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。

事务T2执行查询语句，在索引b=8上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。由于请求的GAP与已持有的GAP是兼容的，因此，事务T2在idx_b索引范围(4, 22)也能加锁成功。

事务T1执行插入语句，会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容，则事务T1等待T2释放GAP锁。

事务T2执行插入语句，也会等待T1释放GAP锁。

于是，死锁便产生了。

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结