锁定MySQL数据库，使一次只能有一个人可以运行查询？ [英] Locking a MySQL database so only one person at once can run a query?

问题描述

我有几个问题，当人们试图访问MySQL数据库，他们正在尝试更新与相同的信息表。

I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.

我有一个网页写使用PHP。在此网页中是一个查询，以检查是否某些数据已输入到数据库中。如果数据没有，然后我继续插入它。麻烦的是，如果两个人同时尝试，检查可能会说数据还没有输入，但插入发生时它已经由对方。

I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.

处理这种情况的最佳方法是什么？

What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?

推荐答案

您正在寻找 LOCK 。

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

这可以作为一个简单的 mysql_query （或 MySQLi :: query / prepare ）运行。

This can be run as a simple mysql_query (or MySQLi::query/prepare).

我会说最好锁定特定的表（虽然你可以尝试 LOCK TABLES * ），需要锁定比整个数据库 - 因为没有什么能够被读取。我想你正在寻找像：

I'd say it's better to lock specific tables (although you can probably try LOCK TABLES *) that need to be locked rather than the whole database - as nothing will be able to be read. I think you're looking for something like:

LOCK TABLES items;
START TRANSACTION;
INSERT INTO items (name, label) VALUES ('foo', 'bar');
UNLOCK TABLES;

或在PHP中：

mysql_query('LOCK TABLES items');
mysql_query("INSERT INTO items (name, label) VALUES ('foo', 'bar')");
mysql_query('UNLOCK TABLES');

这篇关于锁定MySQL数据库，使一次只能有一个人可以运行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！