MySQL Forums
Forum List  »  Performance

Re: UPDATE - Increment Speed Question
Posted by: James Day
Date: April 05, 2005 01:36AM

The second form may not work reliably. Consider:

Session A reads count. It's 320.
Session B reads count. It's 320.
Session A adds 1 to count and saves new count, value 321.
Session B adds 1 to the count it read and saves new count, value 321.

Count is now wrong by one. You can dodge this with SELECT... FOR UPDATE but that's not the best way to do it.

If you use count=count+1 both connections can get it right.

The count=321 form has to do at least two exchanges with the server, once to read the value, then to set the value. The count=count+1 form can be done with a single exchange so it'll be faster in elapsed time.

If using InnoDB you should also read http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html

Options: ReplyQuote


Subject
Views
Written By
Posted
11542
dbv
April 04, 2005 09:44AM
Re: UPDATE - Increment Speed Question
3429
April 05, 2005 01:36AM
2870
dbv
April 05, 2005 09:23AM
2815
April 10, 2005 12:00PM
2685
dbv
April 11, 2005 05:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.