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

连续读不能满足的需求:

我们在讲述隔离级别的时候讲述的时候给过事务一致读的例子，但是有些情况下我们利用MYSQL的一致读却不能满足我们的需求，比如：我们有个系统需要对一个表的ID进行编号max(id)+1：

SESSION 1：

mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.09 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.00 sec)

SESSION 2：

mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

SESSION 1：

mysql> insert into t values(2);
Query OK, 1 row affected (0.20 sec)

mysql> commit;
Query OK, 0 rows affected (0.11 sec)

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
+------+
3 rows in set (0.00 sec)

这样2个SESSION同时进行操作可能会得到相同的编号。