This is the update statement that I have, which seems to be doing what i want it to do, i.e. increment the counter column on specific records of the given table:
update config_per_geo
inner join country on config_per_geo.country_id = country.id
set counter = counter + 1
where product_id in (?, ?, ?)
and (country.iso_code = ? or
(country.iso_code = 'ZZZ' and product_id not in
(select product_id
from (select * from config_per_geo) as cpg1
inner join country as cty1 on cpg1.country_id = cty1.id
where cty1.iso_code = ?)
)
)
(cpg1 aliasing seems to be necessary because of this limitation)
The thing is that this query should be performed from the web application (per HTTP request), where multiple simultaneous requests are possible, in other words in a concurrent, multi-threaded environment. Further more, I'd need to reset the counter field once a day, and I thought I'd perform this through a CRON job, or something, by running:
start transaction;
select counter from config_per_geo for update;
update config_per_geo set counter = 0;
commit;
The questions: what I wonder about is whether these two queries (transactions) will always leave counter column in the consistent state, and more specifically - will the first query consistently and atomically increment the counter when executed concurrently (from simultaneous request threads) ?
I suppose they should, but I couldn't for the life of me be 100% sure based on MySQL documentation...
