I'm writing an online game, there is a section named send troops. When two or more users on one account try to send one movement the troops get doubled.
I want to get a live version of the row from mysql and prevent any read, write, update anything on that row untill I finish.
Is it actually possible? Because I sae only select for update and lock in share mode in innodb reference.
Any help is appericiated.
BEGIN;
SELECT ... FROM t ... WHERE ... FOR UPDATE;
...
UPDATE t ...;
COMMIT;
Others can read the rows from t, but they will either be delayed or deadlocked if they try to modify the row(s) touched by the SELECT.
Do you really need to prevent all reads for a given row? Please explain your scenario further.