Mysql InnoDB中的查询事务模式与锁定select ..for update
Mysql InnoDB中的查询事务模式与锁定select ..for update
在 InnoDB 的行锁中使用所谓的 next-key locking。这就意味着，除了索引记录外，InnoDB 还可以锁定该索引记录前部“间隙” ('gap') 以阻塞其它用户在索引记录前部的直接插入。next-key lock 意思是锁定一个索引记录以及该记录之前的间隙(gap)。gap lock 就是只锁定某些索引记录之前的间隙。
Consistent read
Consistent read 就是 InnoDB 使用它的多版本(multiversioning)方式提供给查询一个数据库在一个时间点的快照。查询将会检查那些在这个时间点之前提交的事务所做的改动，以及在时间点之后改变或未提交的事务? 与这个规则相例外的是查询将检查查询自身发出的事务所做的改变。
如果以默认的 REPEATABLE READ 隔离级，那么所有在同一事务中的 consistent reads 只读取同一个在事务中第一次读所确定的快照。你可以通过提交当前事务并发出一个新的查询以获得新的数据快照。
Consistent read 在 InnoDB 处理 SELECT 中的默认模式是 READ COMMITTED 和 REPEATABLE READ 隔离级。Consistent read 对其所访问的表不加任何锁定，因而其它任何用户均可以修改在 consistent read 被完成之前自由的修改这些表。
Locking reads
Consistent read (mysql默认) 未提交读,允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
方式:select .......
Consistent read 在某些情况下是不太方便的。 假设你希望在表 CHILD 中插入 一个新行，而这个子表已有一个父表 PARENT。
假设你使用 consistent read 了读取表 PARENT 并查看子表中对应记录。你真的能安全地在表 CHILD 中加入一个子行？不可能，因为在此期间可能有其它用户删除了表 PARENT 中的父行，而你并不知道它。
提交读(Read Committed)只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别
方式: SELECT..... LOCK IN SHARE MODE; //会产生死锁 共享访问锁 //一般
在共享模式下执行读取的意思就是读取最新的现有资料，并在所读取的行上设置一个共享模式的锁定。如果最新的数据属于其它用户仍未提交的事务，那将不得不等到这个事务被提交。共享模式的可以防止其它用户更新或删除我们当前所读取的行。当查询获得 Jones后，就可以安全地向子表 CHILD 中加入子行，然后提交事务。这个例子显示如何在应用程序代码中实现参照完整性。
另外一个例子： 在表 CHILD_CODES 有一个整型计数字段用于给在表 CHILD 中加入的每个子行赋于一个唯一的标识符。显而易见地，用一个 consistent read 来读取父表中的值并不是一个好的主意，因两个用户有可能会读取出同一个计数值，当以同一个标识符插入两个字行时将会产生一个重复键值(duplicate key)的错误。如果两个用户同时读取了计数器，当尝试更新计数器时，他们中的一个必将在死锁中结束，所以在读取时使用 LOCK IN SHARE MODE 也并不是一个好的解决办法。
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读 (幻象读。多锁住一些没用的行 下面有详细)
SELECT ... FOR UPDATE //安全最高
在这和情况下有两种方法来实现读取并增加计数器：(1) 首先更新计数器然后再读取它；(2) 首先以一个 FOR UPDATE 方式锁定后再读取，然后再增加它：
SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;
SELECT ... FOR UPDATE
将读取最新的现有数据，并在所读取的行上设置排它的锁定。
同样在 SQL UPDATE 所访问的行上也设置此锁定。
Next-key locking: avoiding the phantom problem 在 InnoDB 的行级锁定上使用一个称作 next-key locking 算法。在 InnoDB 在搜索或扫描表的索引时将进行行锁，它将在所访问到的索引上设置共享或排它的锁定。因而行锁是更加精确地而又称为索引记录锁定。 InnoDB 在索引记录上设置的锁同样会影响索引记录之前的“间隙(gap)”。如果一个用户对索引记录 R 加了一个共享或排它的锁定，那其它用户将不能在 R 之前立即插入新的记录。这种间隙锁定用于防止所谓的“phantom problem”。假设需读取和锁定表 CHILD 中标识符大于 100 的子行，并更新所搜索到的记录中某些字段。
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE; 假设表 CHILD 中有一个索引字段 ID。我们的查询将从 ID 大于100的第一条记录开始扫描索引记录。 现在，假设加在索引记录上的锁定不能阻止在间隙处的插入，一个新的子记录将可能在事务处理中被插入到表中。
如果现在在事务中再次执行 SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE; 在查询返回的记录集中将会有一个新的子记录。这与事务的隔离规则相违背的：一个事务必须能够顺串(run)， 因而在事务处理中所读取的数据将不会发生改变。而新的 'phantom' 子记录将会打破这个隔离规则。
当 InnoDB 扫描索引时，它同样会锁定在索引中在结尾记录(the last record)之后的间隙。这仅仅在上例中会发生： InnoDB 设置的锁定将阻止任何 ID 大于 100 的插入。 在应用程序中可以通过一个 next-key locking 来实现一个唯一性(uniqueness)检查：如果以一个共享模式读取数据并没有发现与将要插入的数据存在重复值， 那么在读取过程中 next-key lock 将被设置在你的记录的后继者(successor)上，这将阻止其它用户在期间插入相同的记录，因而你可以安全地插入你的记录。 所以， next-key locking 可以允许你 'lock' 你的表中并不存在的记录。 InnoDB 中的隔离级详细描述： READ UNCOMMITTED 这通常称为 'dirty read'：non-locking SELECTs 的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离度下是非 'consistent' reads；另外，这级隔离的运作如同 READ COMMITTED。
READ COMMITTED 有些类似 Oracle 的隔离级。所有 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句只锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。以一个唯一地搜索条件使用一个唯一索引(unique index)的 UPDATE 和 DELETE，仅仅只锁定所找到的索引记录，而不锁定该索引之前的间隙。但是在范围型的 UPDATE and DELETE 中，InnoDB 必须设置 next-key 或
gap locks 来阻塞其它用户对范围内的空隙插入。 自从为了 MySQL 进行复制(replication)与恢复(recovery)工作'phantom rows'必须被阻塞以来，这就是必须的了。Consistent reads 运作方式与 Oracle 有点类似： 每一个 consistent read，甚至是同一个事务中的，均设置并作用它自己的最新快照。
REPEATABLE READ 这是 InnoDB 默认的事务隔离级。. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, 和 DELETE ，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。否则这些操作将使用 next-key 锁定，以 next-key 和 gap locks 锁定找到的索引范围，并阻塞其它用户的新建插入。在 consistent reads 中，与前一个隔离级相比这是一个重要的差别：在这一级中，同一事务中所有的
consistent reads 均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的 SELECTs ，这些 SELECTs 的相互关系是一致的。
SERIALIZABLE 这一级与上一级相似，只是无格式(plain)的 SELECTs 被隐含地转换为 SELECT ... LOCK IN SHARE MODE。
