Locking MySQL Database Until First User Has Finished

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
djwk
Forum Commoner
Posts: 56
Joined: Tue Mar 07, 2006 2:14 pm

Locking MySQL Database Until First User Has Finished

Post by djwk »

I have a table in a MySQL database that is accessed regulary by 3 client computers.

Just recently I have been finding a few problems and I can only guess it is down to the multiple access. Each time this table is accessed by a client the following occurs:

A read statement: e.g. SELECT * FROM blah WHERE empty=true
An update statement: e.g. UPDATE blah SET empty=true WHERE id=returned_id

I'm guessing what's happening is 2 clients are returning the same id from the SELECT statement then updating the same record in the UPDATE statement.

Is there any way for me to "lock" the database before client 1 executes any queries? Obviously forcing client 2 to wait until the database is "unlocked" eliminating duplicate access.

Just so you know, I wrote the code for this a long time ago when there was only one client computer, the project kind of grew unexpectedly.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Locking MySQL Database Until First User Has Finished

Post by Christopher »

Look into Transactions or for a more complete solution Optimistic Offline Lock and Pessimistic Offline Lock.
(#10850)
djwk
Forum Commoner
Posts: 56
Joined: Tue Mar 07, 2006 2:14 pm

Re: Locking MySQL Database Until First User Has Finished

Post by djwk »

Look into Transactions or for a more complete solution Optimistic Offline Lock and Pessimistic Offline Lock.
Hi Christopher,

Thank you for your reply, I quickly read through the documentation for MySQL transactions this morning and I must admit did not read thoroughly.

I edited the code on our server before going in to work:

Code: Select all

mysql_query("START TRANSACTION;");
//The original code that was causing problems when accessed simultaneously.
mysql_query("COMMIT;");
No errors have occured when testing this code so far, I would just like to know, is this the correct usage for what I am trying to achieve?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Locking MySQL Database Until First User Has Finished

Post by pickle »

Basically, ya. You want to make sure the original code runs successfully though. If it fails, you want to run a ROLLBACK command rather than a COMMIT command. Also, the default MyISAM table format doesn't support transactions, you need InnoDB.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Locking MySQL Database Until First User Has Finished

Post by Christopher »

Though I am not sure a Transaction will help here. The SELECT and UPDATE may succeed without errors -- even though an UPDATE by another process has happened in between. That is why you may need to implement a lock.
(#10850)
Post Reply