Geek Answers Handbook

MySQL database lock so that only one person can run a query?

I have several problems when people try to access a MySQL database and they try to update tables with the same information.

I have a webpage written using PHP. This web page contains a request to check whether certain data has been entered into the database. If there is no data, I proceed to insert it. The trouble is that if two people try at the same time, the check may say that the data has not yet been entered, but when the insert occurs, it was another person.

What is the best way to handle this scenario? Can I lock the database to process only my requests and then others?

+5
source share
4 answers

You are looking for LOCK.

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

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

I would say that it is better to block certain tables (although you can probably try LOCK TABLES *) that you need to block, rather than the entire database - since nothing can be read. I think you are looking for something like:

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

Or in PHP:

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

Read database transactions . This is probably the best way to handle what you need than run LOCK TABLES.

+5
source

- , . , ( PHP , ..).

- - , . , , , . , EDIT , , , , .

, datetime , , , .

+3

, , - . , - , , .

stackoverflow .

0

More articles:


All Articles