使用存储过程并发访问MySQL数据库 [英] 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?

谢谢.

推荐答案

首先，如另一篇文章所述，使用InnoDB.它是MySQL 5.5的默认存储引擎，功能更强大.

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

其次，请查看此页面: http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

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

您应该使用SELECT ... FOR UPDATE来防止其他连接读取将要更新的行，直到事务完成为止:

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;

这比锁定表更好，因为InnoDB会进行行级锁定.上面的事务只会锁定id = 5 ...的行，因此该id不会影响另一个ID = 10的查询.

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.

这篇关于使用存储过程并发访问MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！