concurrent access to MySQL database using stored procedure

Question

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.

Answer 1

First, as stated in another post, use InnoDB. It is the default storage engine as of MySQL 5.5 and is more robust.

Second, look at this page: http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

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.

Answer 2

if possible you can lock the table just before calling the SP then unlock immediately after. i had i similar problem and this is how i circumvented this issue.

example

LOCK TABLES my_table LOW_PRIORITY WRITE;
CALL my_stored_procedure('ff');
UNLOCK TABLES;