CODEWITHSUNDEEP
mysqlstored-proceduresconcurrency
usp
usp

Reputation: 797

concurrent access to MySQL database using stored procedure

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.

Upvotes: 13

Views: 18484

Answers (4)

bobwienholt
bobwienholt

Reputation: 17610

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.

Upvotes: 21

hyena
hyena

Reputation: 765

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;

Upvotes: 2

Nick
Nick

Reputation: 2413

Create a separate table (or reuse the original if appropriate) for all of the incremental inserts and use a SUM() for retrieval.

If there's still a concern about the number of eventual rows, then use a transaction to sum them into a single row back in the original table periodically. Most likely the trade-off of eventual consistency in the sum (reads lagging the writes) or performance hit in summing rows is less of an issue when compared to the stalls on multiple writers waiting on a lock to a single row.

Upvotes: 0

Imdad
Imdad

Reputation: 6042

Use Innodb. Inside the stored procedure start transaction before do anything else. At the Commit and end the transaction. This will resolve the read/write problem.

But be aware of the fact that it will slow down concurrent operation. It is fine for the case only a few concurrent request are expected at a given time.

Upvotes: 0

Related Questions