innodb 锁定读取（当前读）

锁定读取（Locking Reads）

当事务A用常规的select语句查询数据后想做更新或插入操作，但常规的select语句不能对这些数据提供很好的保护。其他的事务可以在A查询之后对查询结果进行更新或删除的操作。

锁定读取语句

innodb额外的提供了两种锁定读取保证数据的安全：
（1）在读取的任何行上设置共享模式锁定。

SELECT ... LOCK IN SHARE MODE

• 其他会话可以读取行，但是在本事务提交之前其他会话不能修改它们。
• 如果要查询的这些行中有任何一行正在被尚未提交的另一个事务更改，则查询将等待直到该事务结束，然后使用最新值。

（2） FOR UPDATE：对于搜索到的每一条索引记录，锁住行和任意遇到的索引条目，就像你对这些行使用了UPDATE语句一样。

SELECT ... FOR UPDATE

• 其他对于这些行将要执行 UPDATE、SELECT … LOCK IN SHARE
  MODE或者在某些隔离级别下想要读取数据的事务都会被阻塞。
• 一致性读取会忽略任何锁，因此不会被阻塞。（记录的旧版本无法锁定；可以通过在记录的内存副本上应用撤消日志来重构它们。）

上述两类语句在处理单个表中或拆分成多个表的树结构或图结构数据时最有用。 您从一处到另一处遍历边缘或树枝，同时保留返回并更改任何这些“指针”值的权利。

锁的释放

当事务提交或回滚时，所有被 LOCK IN SHARE MODE 或 FOR UPDATE 设置的锁都会释放。

锁定读取的前提

仅当禁用了自动提交（通过使用START TRANSACTION开始事务或将自动提交设置为0）时，才可以进行锁定读取。

锁定读取的作用域

除非在子查询中还指定了锁定读取，否则外部语句中的锁定读取不会锁定嵌套子查询中表的行。 例如，以下语句不会锁定表t2中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

以下语句则会锁住t2中的行：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

锁定读取的例子

一、 LOCK IN SHARE MODE

不同表之间的信息有关联时，用SELECT … LOCK IN SHARE MODE 查询一个表的信息后再在另一张表中更新

设想一下你想在child表中插入一行，并且事先要确保这一行在parent表中有对应的记录。你的应用程序代码应该确保在此操作序列中的引用完整性。

错误的做法是：先用一致性读取查询parent表，确保parent表中对应的行存在，然后在child中插入一行。因为在你使用 SELECT 和 UPDATE 之间，其他的会话很有可能已将把对应的parent表中的记录删除了，而你并没有察觉到。

为了避免这种潜在的隐患，使用LOCK IN SHARE MODE：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

在使用LOCK IN SHARE MODE查询完parent表中的NAME为Jones的记录后，你可以安全的在child表中添加记录并且提交事务。其他的会话如果想获取parent表中这行记录的排他锁必去等你的事务结束。

二、FOR UPDATE

同一个表中的字段值在更新时依赖于先前的值，用FOR UPDATE更新。

设想一下在 CHILD_CODES 表中有一个用于统计child个数的整型字段counter_field ，该字段用于唯一标示child表中的每一个child。

不要用一致性读取或者共享模式的读取来读取计数器的当前值，因为不同的使用者在更新前可能会查询到相同的值，当他们将相同的更新值插入时，会出现重复键的错误。

LOCK IN SHARE MODE在这种情况下不好使的原因是：当两个事务在共享模式下同时读取一条记录时，如果他们接下来都要做更新操作，那么就必须等对方释放锁，从而陷入死锁。

如果想读取并增加counter_field值，先要用 FOR UPDATE 的锁定读取，然后在更新counter_field值：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT … FOR UPDATE 语句会读取最新的值，并在在读取的每一行上设置排他锁。因此，它设置了与包含搜索条件的UPDATE相同的锁。

前面的描述只是展示 SELECT … FOR UPDATE 如何工作的示例。 在MySQL实际操作中，仅通过单次访问表就可以完成生成唯一标识符的特定任务：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT语句仅检索标识符信息（用于当前连接）。 它不访问任何表。