mysql存储过程并发_使用存储过程并发访问MySQL数据库

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.