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.
Locking MySQL Database Until First User Has Finished
Moderator: General Moderators
- 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
Look into Transactions or for a more complete solution Optimistic Offline Lock and Pessimistic Offline Lock.
(#10850)
Re: Locking MySQL Database Until First User Has Finished
Hi Christopher,Look into Transactions or for a more complete solution Optimistic Offline Lock and Pessimistic Offline Lock.
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;");Re: Locking MySQL Database Until First User Has Finished
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.
- 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
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)