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=arch...@jab.org
