Cached mysql inserts - Preserving Data integrity

I would like to do a lot of inserts, but could it be possible to update mysql after a while.

For example if there is a query such as

Update views_table SET views = views + 1 WHERE id = 12;

Could it not be possible to maybe store this query until the views have gone up to 100 and then run the following instead of running the query from above 100 times.

Update views_table SET views = views + 100 WHERE id = 12;

Now, lets say that is done, then comes the problem of data integrity. Let's say, there are 100 php files open which are all about to run the same query. Now unless there is a locking mechanism on incrementing the cached views, there is a possibility that multiple files may have a same value of the cached view, so lets say 开发者_如何学JAVAprocess 1 may have 25 cached views and php process 2 may have 25 views and process 3 may have 27 views from the file. Now lets say process 3 finishes and increments the counter to 28. Then lets say php process is about finish and it finished just after process 3, which means that the counter would be brought back down to 26.

So do you guys have any solutions that are fast but are data secure as well.

Thanks

---

As long as your queries use relative values views=views+5, there should be no problems.

Only if you store the value somewhere in your script, and then calculate the new value yourself,you might run into trouble. But why would you want to do this? Actually, why do you want to do all of this in the first place? :)

If you don't want to overload the database, you could use UPDATE LOW_PRIORITY table set ..., the LOW_PRIORITY keyword will put the update action in a queue and wait for the table to no longer be used by reads or inserts.

---

First of all: with these queries: regardless of when a process starts, the UPDATE .. SET col = col + 1 is a safe operation, so it will not 'decrease' the counter, ever.

Regarding to 'store this query until the views have gone up to 100 and then run the following instead of running the query from above 100 times': not really. You can store a counter in faster memory (memcached comes to mind), with a process that transfers it to the database once in a while, or store it in another table with a AFTER UPDATE trigger, but I don't really see a point doing that.