Page 1 of 1

Locking MySQL Database Until First User Has Finished

Posted: Mon Jul 09, 2012 4:55 pm
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.

Re: Locking MySQL Database Until First User Has Finished

Posted: Mon Jul 09, 2012 5:43 pm
by Christopher
Look into Transactions or for a more complete solution Optimistic Offline Lock and Pessimistic Offline Lock.

Re: Locking MySQL Database Until First User Has Finished

Posted: Tue Jul 10, 2012 4:33 pm
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?

Re: Locking MySQL Database Until First User Has Finished

Posted: Wed Jul 11, 2012 9:47 am
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.

Re: Locking MySQL Database Until First User Has Finished

Posted: Wed Jul 11, 2012 9:59 am
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.