Copyright (C) 2000-2012 |
GNU Info (mysql.info)InnoDB locking readsLocking reads ............. A consistent read is not convenient in some circumstances. Suppose you want to add a new row into your table `CHILD', and make sure that the child already has a parent in table `PARENT'. Suppose you use a consistent read to read the table `PARENT' and indeed see the parent of the child in the table. Can you now safely add the child row to table `CHILD'? No, because it may happen that meanwhile some other user has deleted the parent row from the table `PARENT', and you are not aware of that. The solution is to perform the `SELECT' in a locking mode, `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. If the latest data belongs to a yet uncommitted transaction of another user, we will wait until that transaction commits. A shared mode lock prevents others from updating or deleting the row we have read. After we see that the above query returns the parent `'Jones'', we can safely add his child to table `CHILD', and commit our transaction. This example shows how to implement referential integrity in your application code. Let us look at another example: we have an integer counter field in a table `CHILD_CODES' which we use to assign a unique identifier to each child we add 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 then two users of the database may see the same value for the counter, and we will get a duplicate key error when we add the two children with the same identifier to the table. 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: SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE; UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1; A `SELECT ... FOR UPDATE' will read 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. automatically generated by info2www version 1.2.2.9 |