In some circumstances, a consistent (nonlocking) read is not convenient and a locking read is required instead. InnoDB supports two types of locking reads:

Locks set by LOCK IN SHARE MODE and FOR UPDATE reads are released when the transaction is committed or rolled back.

As an example of a situation in which a locking read is useful, 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. The following discussion describes how to implement referential integrity in application code.

Suppose that you use a consistent read to read the table parent and indeed see the parent row of the to-be-inserted child row in the table. Can you safely insert the child row to table child? No, because it is possible for some other session to delete the parent row from the table parent in the meantime without you being aware of it.

The solution is to perform the SELECT in a locking mode using LOCK IN SHARE MODE:

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

A read performed with LOCK IN SHARE MODE reads the latest available data and sets a shared mode lock on the rows read. A shared mode lock prevents others from updating or deleting the row read. Also, if the latest data belongs to a yet uncommitted transaction of another session, we wait until that transaction ends. After we see that the LOCK IN SHARE MODE query returns the parent 'Jones', we can safely add the child record to the child table and commit our transaction.

Let us look at another example: We have an integer counter field in a table child_codes that we use to assign a unique identifier to each child added to table child. It is not a good idea to use either consistent read or a shared mode read to read the present value of the counter because two users of the database may then see the same value for the counter, and a duplicate-key error occurs if two users attempt to add children with the same identifier to the table.

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.

In this case, there are two good ways to implement reading and incrementing the counter:

The latter approach can be implemented as follows:

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

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.

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:

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.