(mysql.info.gz) InnoDB locking reads

Info Catalog

(mysql.info.gz) InnoDB consistent read

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB Next-key locking

 
 15.11.4 Locking Reads `SELECT ... FOR UPDATE' and `SELECT ... LOCK IN SHARE MODE'
 ---------------------------------------------------------------------------------
 
 In some circumstances, a consistent read is not convenient.  For
 example, you might want to add a new row into your table `child', and
 make sure that the child has a parent in table `parent'.  The following
 example shows how to implement referential integrity in your
 application code.
 
 Suppose that you use a consistent read to read the table `parent' and
 indeed see the parent of the child in the table. Can you safely add the
 child row to table `child'? No, because it may happen that meanwhile
 some other user deletes the parent row from the table `parent', 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;
 
 Performing a read in share mode means that we read the latest available
 data, and set a shared mode lock on the rows we read.  A shared mode
 lock prevents others from updating or deleting the row we have read.
 Also, if the latest data belongs to a yet uncommitted transaction of
 another client connection, we will wait until that transaction commits.
 After we see that the preceding 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'.  Obviously, using a consistent read or a
 shared mode read to read the present value of the counter is not a good
 idea, since two users of the database may then see the same value for
 the counter, and a duplicate-key error will occur 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 will end up in
 deadlock when attempting to update the counter.
 
 In this case, there are two good ways to implement the reading and
 incrementing of the counter: (1) update the counter first by
 incrementing it by 1 and only after that read it, or (2) read the
 counter first with a lock mode `FOR UPDATE', and increment after that.
 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.
 
 Please note that the above 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.
 

Info Catalog

(mysql.info.gz) InnoDB consistent read

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB Next-key locking