Mysql-锁定读(Locking Reads)

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.

设想一下你想要在一张名为child的表中插入一条新行，同时要确保新插入的这条child行在另一张名为parent的表中有一条parent行与之对应(译者注：意思就是说要保证两张表的参照完整性，不能在你往一张表插入数据的时候另一张表中对应的记录被删除了，这样就违反了参照完整性，也会使得数据库产生脏数据。其实如果我们设置了外键就不会出现这个问题了，会抛主表找不到对应记录的异常的，但是不要这么寻根究底了，毕竟这里只是举个锁的例子。)。你的代码可以通过序列操作来保证参照完整性。

First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.

首先，如果通过一致性读(就是普通读)查询PARENT表然后验证parent 行记录存在。这样可以把child行记录安全的插入CHILD表吗？答案是不安全的，因为与此同时其它的事务绘画可以在你执行SELECT和INSERT之间的一瞬间删除对应的parent行记录，而你还没有意识到。

To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:

为了避免这个潜在的问题，可以在LOCK IN SHARE MODE(共享锁模式)下执行SELECT：

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

After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

在共享锁模式下查询并返回parent行记录'Jones'以后，你可以安全的在CHILD表中添加对应的child行，然后提交事务。任何事务试图获得在PARENT表对应的记录行的独占锁(译者注：即写入的意思，因为写入会自动获取排它锁)都会等待直到你的共享锁模式读所在的事务结束为止，也就是说，直到所有表中的数据处于一致状态(这后半句没看懂)。

For another example, consider an integer counter field in a table CHILD_CODES, used to assign a unique identifier to each child added to table CHILD. Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter, and a duplicate-key error occurs if two transactions attempt to add rows with the same identifier to the CHILD table.

举另外一个例子，CHILD_CODES表中有一个整型字段，用来给CHILD表中每一个child记录分配一个唯一id。如果不使用一致性读或者共享锁读 去读取计数器当前值，由于有可能多个用户同时从计数器获取到同一个值，然后多个事务同时添加行到CHILD表中，这时候就会报key冲突错误。

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.

这种情况，共享锁模式是不太适用的，因为如果两个用户同时读计数器，然后同时去update计数器的时候，至少有一个会报死锁错误。

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

为了实现读取然后递增计数器，首先需要使用FOR UPDATE 排它锁锁定读计数器，然后再递增计数器。如下：

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

使用SELECT ... FOR UPDATE读取最新的有效数据，在读取的行上面设置排它锁。UPDATE也会设置相同的锁在这些行上。

The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

前面的描述仅仅是 SELECT ... FOR UPDATE 是怎样工作的一个例子。其实在MySQL中，生成唯一标识符只需要对表进行一次访问就可以了：

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

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.