CODEWITHSUNDEEP
phpmysqlconcurrency
Gary Willoughby
Gary Willoughby

Reputation: 52498

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

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?

Upvotes: 5

Views: 8922

Answers (4)

TravisO
TravisO

Reputation: 9540

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.

Upvotes: 4

Ross
Ross

Reputation: 46987

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');

Upvotes: 2

Jason S
Jason S

Reputation: 189626

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

Upvotes: 5

Maiku Mori
Maiku Mori

Reputation: 7469

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.

Upvotes: 0

Related Questions