Locking a MySQL database so only one person at once can run a query?

Question

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.

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?

Answer 1

You're looking for LOCK.

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

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

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;

Or in PHP:

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

Answer 2

You could check if data has been changed before you edit something. In that case if someone has edited data while other person is doing his edit, he will be informed about it.

Kind of like stackoverflow handles commenting.

Answer 3

Read up on database transactions. That's probably a better way to handle what you need than running LOCK TABLES.

Answer 4

Manually locking tables is the worst think you could ever do. What happens if the code to unlock them never runs (because the PHP fails, or the user next clicks the next step, walks away from the PC, etc).

One way to minimize this in a web app, and a common mistake devs do, is to have a datagrid full of text boxes of data to edit, with a save button per row or on the whole table. Obviously if the person opens this on Friday and comes back Monday, the data could be wrong and they could be saving over new data. One easy way to fix this is to instead have EDIT buttons on each row, and clicking the button then loads an editing form, this way they are hopefully loading fresh data and can only submit 1 row change at a time.

But even more importantly, you should include a datetime field as a hidden input box, and when they try to submit the data look at the date and decide how old the data is and make a decision how old is too old and to warn or deny the user about their action.