MySQL-SQL语句与锁

MySQL在处理SQL语句的时候，不管SQL语句是什么WHERE条件，UPDATE语句或者DELETE语句的有锁读通常对其扫描的每个索引记录都设置锁，因为，InnoDB在处理行记录的时候不会识别与记住WHERE条件，只会识别并记住其扫描的索引范围。如果使用的是下一键锁（next-key locks），InnoDB也会对索引记录之前的地址间隙设置锁，MySQL也提供可设置的属性选项用于关闭间隙锁的功能，在这种情况下，下一键锁的功能也会被关闭。

如果SQL语句检索使用的是非主键索引，则InnoDB对这些索引记录设置排他锁，同时，InnoDB也会对这些非主键索引对应的主键索引记录设置排他锁。

如果SQL语句检索没有索引可使用，则MySQL执行全表扫描，对数据表中的每个行记录都设置锁，如果同时存在多个用户执行这种类型的检索，则InnoDB轮流阻塞其他用户的插入操作，同时一次只能允许一个用户执行插入操作。因此，对每个数据表都建立索引，是非常必要而且非常重要，其好处是在行记录检索的时候不必要执行全部扫描，而只需要扫描指定范围内的行记录。

InnoDB对不同的语句类型设置不同的锁类型，其描述如下所示：

• 语句SELECT ... FROM使用一致性读，即从数据库中读取一个快照版本，并没有设置任何锁，除非使用的是串行化事务隔离级别，如果使用串行化事务隔离级别，在检索中对遇到的每个索引记录都设置共享下一键锁。而索引记录锁只在具有唯一性索引的数据表中检索唯一性记录的语句中才生效。

• 语句SELECT ... FOR UPDATE与语句SELECT ... FOR SHARE使用唯一性索引获取被扫描的行记录的锁，不匹配的行记录的锁将被立刻释放（不匹配WHERE语句的条件的记录）。但是，在一些异常情况下，锁并没有被立刻释放，例如，当使用UNION执行联合查询的时候，有些行记录已经被移动而引起源数据表的行记录丢失，这种异常情况下只有在事务提交完成之后，锁才被完全释放。

• 对于有锁读（语句SELECT ... FOR UPDATE或者语句SELECT ... FOR SHARE）的UPDATE更新语句或者DELETE删除语句，如何设置锁取决于语句使用的是唯一性索引检索唯一性记录还是范围检索，其分类如下所示

  唯一性索引检索唯一性记录，InnoDB只对对应索引记录设置锁，并没有对索引记录之前的地址间隙设置锁。

  其他检索条件，范围检索或者非唯一性索引检索，InnoDB对扫描的索引范围设置锁，使用间隙锁或者下一键锁阻塞其他会话在间隙中的执行插入操作。

• 对于检索的是索引记录，语句SELECT ... FOR UPDATE阻塞其他会话执行语句SELECT ... FOR SHARE或者其他读隔离级别的语句，则不会阻塞对这些被设置锁的行记录执行一致性读的操作。

• 语句UPDATE ... WHERE ...对检索遇到的每个行记录都设置排他下一键锁，而只有在WHERE是唯一性索引的唯一性条件的情况下是设置了索引记录锁。

• 当UPDATE更新语句更新的是主键索引，InnoDB也隐式地对主键索引相关的非主键索引设置锁，在插入非主键索引记录的时候，InnoDB也先设置共享锁用于判断是否存在重复键的异常情况，再执行插入新记录的操作。

• 语句DELETE FROM ... WHERE ...对检索遇到的每个行记录都设置排他下一键锁，而只有在WHERE是唯一性索引的唯一性条件的情况下是设置了索引记录锁。

• 语句INSERT对插入的行记录设置排他锁，该锁是一个索引记录锁，而不是一个下一键锁，该锁不能阻止其他会话在待插入记录之前的间隙中插入记录。

  在插入新的行记录之前，设置一个插入意向的间隙锁，设置该类型锁的作用是发布一个即将插入对应间隙的信号，该信号表明，允许多个事务同时在该间隙中插入新的行记录而不会相互阻塞，除非同时存在多个事务在间隙的相同位置上插入相同的记录。假设，已经存在索引值是4、7的行记录，其他两个事务分别需要插入索引值是5、6的行记录，这两个事务在获取到插入排他锁之前需要分别在索引值4、7之间的间隙中设置一个插入意向的间隙锁，但是这两个事务也不会相互阻塞，因为它们的索引值5、6不是相同的值，因而不会发生冲突。

  如果发生重复键的错误，则对发生重复键的索引设置一个共享锁，在这种情况下，使用共享锁可能发生deadlocks，例如，同时存在多个事务试图插入行记录，其他一个事务已经获取到排他锁，而其他一个事务又删除了该记录，则发生deadlocks，举例如下所示，先创建如下结构的数据表t1，假设，存在三个会话，其执行的步骤顺序如下所示。

  

  会话1：

  

  会话2：

  

  会话3：

  

  会话1：

  

  如上所示的执行流程，会话1先获取到排他锁，会话2与会话3由于插入的索引值相同而发生重复键的错误，随后，会话2与会话3请求获取索引值等于1的行记录的共享锁，随后，会话1执行回滚操作并且释放了排他锁，随后，会话2与会话3按照队列的顺序分别获取到共享锁，此时，发生deadlocks，会话2与会话3都获取不到排他锁，因为，会话2请求获取排他锁的时候，发现会话3持有的共享锁限制了写操作，同理，会话3请求获取排他锁的时候，发现会话2持有的共享锁限制了写操作，因此，会话2与会话3互相持有读的权限而又不互相释放写的权限。

  假设，数据表t1已经存在一条索引值等于1的记录，如下所示的操作顺序也会发生deadlocks。

  会话1：

  

  会话2：

  

  会话3：

  

  会话1：

  

  如上所示的执行流程，会话1先获取到排他锁，会话2与会话3由于插入的索引值相同而发生重复键的错误，随后，会话2与会话3请求获取索引值等于1的行记录的共享锁，随后，会话1执行提交的操作并且释放了排他锁，随后，会话2与会话3按照队列的顺序分别获取到共享锁，此时，发生deadlocks，会话2与会话3都获取不到排他锁，因为，会话2请求获取排他锁的时候，发现会话3持有的共享锁限制了写操作，同理，会话3请求获取排他锁的时候，发现会话2持有的共享锁限制了写操作，因此，会话2与会话3互相持有读的权限而又不互相释放写的权限。

• 语句INSERT ... ON DUPLICATE KEY UPDATE与语句INSERT的区别是，在发生重复键错误的时候，在冲突的键上设置了排他索引记录锁而不是设置了共享锁，也设置排他下一键锁。

• 语句REPLACE的处理与语句INSERT的处理类似，在发生重复键错误的时候，设置了排他下一键锁。

• 语句INSERT INTO T SELECT ... FROM S WHERE ...对T中的每个插入记录都设置了排他锁，如果使用READ COMMITTED的事务隔离级别，则InnoDB对S执行一致性读。语句CREATE TABLE ... SELECT ...设置了共享下一键锁或者执行一致性读。语句REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)对s设置了共享下一键锁。

• InnoDB在AUTO_INCREMENT自动递增列的最后一个索引位置设置了一个排他锁。当使用属性选项innodb_autoinc_lock_mode=0，InnoDB对自动递增列设置表级锁（AUTO-INC自动递增锁），当innodb_autoinc_lock_mode=1，InnoDB也对批量插入设置表级锁，当innodb_autoinc_lock_mode=2不设置表级锁。

• 外键设置共享记录锁，约束增加、更新、删除的操作，检查外键的约束关系的一致性。

• 语句LOCK TABLES设置表级锁，MySQL是在InnoDB存储引擎层以上设置了表级锁，在属性选项innodb_table_locks = 1 (默认) 与autocommit = 0的情况下表级锁才生效。

• 当事务被提交或者事务被回滚，则InnoDB持有的所有对应的锁都被释放。

• 不能在执行一个事务的过程中对其他表执行锁的操作。