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: phark...@gmail.com [mailto:phark...@gmail.com] On Behalf Of Perrin Harkins Sent: Friday, January 15, 2010 6:08 AM To: Johan Machielse Cc: Baron Schwartz; mysql@lists.mysql.com 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.machie...@kpnplanet.nl> 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=gto...@ffn.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
