How can i update table when counter reach a specific value? (thread safe) I increase a field when somebody click the image. Also i want to update (decrease) customer budget when counter value reach to 1k. It is like that:

UPDATE click SET count = count + 1 WHERE customer_id = 1;
UPDATE budget JOIN click ON budget.customer_id = click.customer_id
 SET budget.total_budget = budget.total_budget - 10, click.count = 0
 WHERE budget.customer_id = 1 AND click.count = 1000;


But it should be thread safe, i don't want to decrease budget 2 times when 2 clicks happen same time. My question is how can do this with mysql?
One update is atomic operation, so when running the second update twice, the second thread has to wait until the first finishes (when using innodb and transactions, entire transaction is atomic) and it should not update anything, because the click.count will be set to 0 before by the other thread.

But I see a potential problem with your code - if other thread updates count to 1001 too fast, your update won't do anything and will never repair it. You should probably do

SET click.count = click.count - 1000 ... WHERE click.count &gt;= 1000


so you can catch such cases. (They are maybe not very probable, but if you have lot of hits, then they can sure happen if you do not prevent them by some other means)