本文源于同事王航威的翻译,对原文做了简单的修改 ,原文地址: http://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html
Locking read( SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE),UPDATE以及DELETE语句通常会对通过索引扫描的记录加上 next-key locks ,忽略没有用到索引的那部分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查询,生成了一张临时表,导致临时表的行记录和原始表的行记录丢失了联系,只能等待查询执行结束才能释放。
http://docs.fordba.com/mysql/refman-5.6-en/innodb-storage-engine.html#innodb-locks-set