Locking MySQL Database Until First User Has Finished
Posted: Mon Jul 09, 2012 4:55 pm
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.
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.