I have a table with two columns: item_id (int, auto inc) and item_counter (int, default value 0)
A user on a web page is allotted a few items by entering a form. This runs:
SELECT * FROM itemtable WHERE item_counter<100 ORDER BY RAND() LIMIT 5
This is followed by an UPDATE query increasing the item_counter of each of those items.
UPDATE itemtable SET item_counter=item_counter + 1 WHERE item_id=:item_id
About 20-50 users will be doing this operation at once.
A simple application. 1 SELECT and 5 UPDATE operations are sequential for each user, and I want the item_counter to be accurate (avoiding this scenario: an item_id is selected as having item_counter 99 but gets updated by some other user before this user is able to update it.
Should I use concurrency/locking in this?
I don't know if InnoDB's row-level locking is inherent to all UPDATE operations or any syntax changes are required?. I'm wondering what should I use here.. or don't use anything at all.