On Fri, Jan 15, 2010 at 2:54 AM, Johan Machielse
> The problem is that multiple users can read and update the same field
> simultaneously (worse case) which could lead to unpredictable problems.
There are other ways to do handle most cases. For example:
UPDATE table SET value = value + 1 WHERE key = 7;
If you need to grab the value after the insert, you can get it from
UPDATE table SET value = last_insert_id(value + 1) WHERE key = 7;
However, if your situation is more complex than that, FOR UPDATE is
usually a good solution.
> What I really want is the following:
> When person A is reading and updating a field value, person B should not be
> able to do this simultaneously. Person B has to wait till the Person A has
> finished his work.
FOR UPDATE is the right solution for that. Your only issue seems to
be that you feel too many rows are being locked. That's an internal
implementation issue, but you may be able to change it by adjusting
which columns have indexes and keeping your statistics up to date. Or
there may not be enough cardinality on the column you're using in the
query to lock specific rows. Using EXPLAIN on the SELECT query might
tell you more about what's happening.