List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:January 18 2010 7:55pm
Subject:RE: When using "FOR UPDATE" whole the table seems to lock instead
of selected row
View as plain text  
I think Baron was referring to a technique like this:

you sell a t-shirt, UPDATE table SET t=t-X WHERE t >= X, if you get rows affected, it's
sold and ok. if not, the stock ran out before the operation. but it's safe. see
http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html


-----Original Message-----
From: pharkins@stripped [mailto:pharkins@stripped] On Behalf Of Perrin Harkins
Sent: Friday, January 15, 2010 6:08 AM
To: Johan Machielse
Cc: Baron Schwartz; mysql@stripped
Subject: Re: When using "FOR UPDATE" whole the table seems to lock instead of selected row

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
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