I have a stored procedure that will read and then increment a value in the database. This particular procedure is used by many programs at the same time. I am concerned about the concurrency issues, in particular the reader-writer problem. Can anybody please suggest me any possible solutions?
thanks.
解决方案
First, as stated in another post, use InnoDB. It is the default storage engine as of MySQL 5.5 and is more robust.
You should use a SELECT ... FOR UPDATE to prevent other connections from reading the row you are about to update until your transaction is complete:
START TRANSACTION;
SELECT value INTO @value
FROM mytable
WHERE id = 5
FOR UPDATE;
UPDATE mytable
SET value = value + 1
WHERE id = 5;
COMMIT;
This is better than locking the table because InnoDB does row level locks. The transaction above would only lock the rows where id = 5... so another query working with id = 10 wouldn't be held up by this query.