MySQL innodb中各种SQL语句加锁分析

Locking read（ SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE），UPDATE以及DELETE语句通常会在他扫描的索引所有范围上加锁，忽略没有用到索引的那部分where语句。
举个例子：

CREATE TABLE `test` ( `id` int(11) NOT NULL DEFAULT '0', `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test where id > 3 and name <'A' for update;

这条SQL语句的会将所有id>3的记录进行加锁，而不是id>3 and name <'A' 进行加锁，因为name上面没有索引。

如果一个SQL通过二级索引进行扫描，并且在二级索引上设置了一个锁，那么innodb将会在对应的聚簇索引记录上也加上一把锁。

如果一个SQL语句无法通过索引进行Locking read，UPDATE，DELETE，那么MySQL将扫描整个表，表中的每一行都将被锁定（在RC级别，通过semi-consistent read，能够提前释放不符合条件的记录，在RR级别，需要设置innodb_locks_unsafe_for_binlog为1，才能打开semi-consistent read）。在某些场景下，锁也不会立即被释放。例如一个union查询，生成了一张临时表，导致临时表的行记录和原始表的行记录丢失了联系，只能等待查询执行结束才能释放。

SQL分析

1.SELECT ... FROM 是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下，如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

SELECT ... FROM ... LOCK IN SHARE MODE 语句在所有索引扫描范围的索引记录上加上共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
SELECT ... FROM ... FOR UPDATE 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。这将堵塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。
UPDATE ... WHERE ...语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。

DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
INSERT 语句将在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap 锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

在插入记录之前，将会加上一种叫做 insert intention gap 的 gap 锁。这个 insert intention gap表示他有意向在这个index gap插入记录，如果其他会话在这个index gap中插入的位置不相同，那么将不需要等待。假设存在索引记录4和7，会话A要插入记录5，会话B要插入记录6，每个会话在插入记录之前都需要锁定4和7之间gap，但是他们彼此不会互相堵塞，因为插入的位置不相同。

如果出现了重复键错误，将在重复键上加一个共享锁。如果会话1插入一条记录，没有提交，他会在该记录上加上排他锁，会话2和会话3都尝试插入该重复记录，那么他们都会被堵塞，会话2和会话3将尝试在该记录上加一个共享锁。如果此时会话1回滚，将发生死锁。

例子如下：表结构：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;