MySQL存储引擎InnoDB 中各 SQL 语句的锁定设置

SELECT ... FROM ...: 这是一个 consistent read，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为SERIALIZABLE，在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。

SELECT ... FROM ... LOCK IN SHARE MODE: 在所读取的所有记录索引上设置同享的锁定。

SELECT ... FROM ... FOR UPDATE: 在所读取的所胡记录索引上设置独占地(exclusive)锁定。

INSERT INTO ... VALUES (...): 在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个 next-key lock ，并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误， 在重复索引记录上设置一个共享的锁定。

如果在一个表中定义了一个AUTO_INCREMENT列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式AUTO-INC，这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。

INSERT INTO T SELECT ... FROM S WHERE ...在已插入到表T中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表S，但是如果 MySQL 打开了日志开关将在表S上设置一个共享的锁定。 在从备份中进行前滚(roll-forward)修复时，每个 SQL 语句必须严格按照原先所执行的顺序运行，所以 InnoDB 不得不设置锁定。

CREATE TABLE ... SELECT ...与上项相似，以 consistent read 或锁定方式完成SELECT。

REPLACE如果没有一个 unique key 冲突，它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。

UPDATE ... SET ... WHERE ...: 在搜索时所遭遇到的记录上设置一个独占的锁定。

DELETE FROM ... WHERE ...: 在搜索时所遭遇到的每一个记录上设置一个独占的锁定。

如果一个表上有FOREIGN KEY约束，所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB 也设置这个锁定。

LOCK TABLES ...: 设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定：查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容： 自从 MySQL 提供行锁以来，将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger.

在 早期 版本以前，SHOW TABLE STATUS应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。 这就意味着SHOW TABLE STATUS可能会引起一个事务的死锁，这可能是我们所意想不到的。从 3.23.50 开始，在读取自增列值时将不再设置任何锁定，除非在某些情况下，比如在数据库启动后没有任何记录。