List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:January 15 2010 2:07pm
Subject:Re: When using "FOR UPDATE" whole the table seems to lock instead of
selected row
View as plain text  
On Fri, Jan 15, 2010 at 2:54 AM, Johan Machielse
<johan.machielse@stripped> wrote:
> 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
last_insert_id:
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.

- Perrin
Thread
When using "FOR UPDATE" whole the table seems to lock instead of selected rowJohan Machielse14 Jan
  • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowBaron Schwartz15 Jan
  • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowJohan Machielse15 Jan
    • Re: When using "FOR UPDATE" whole the table seems to lock instead of selected rowPerrin Harkins15 Jan
      • RE: When using "FOR UPDATE" whole the table seems to lock insteadof selected rowGavin Towey18 Jan