Page 1 of 1

How to lock,unlock MYSQL tables?

Posted: Fri Jul 17, 2009 3:09 am
by adisrikanth
Hii every one..

I am fresher in PHP. I am developing a site using PHP,AJAX and MYSQL.

Actually mysite requires maximum use of mysql databases. If every thing goes right my site might have visitors of more than 10 users accesing the mysql tables at a time indirectly thgh site. my problem is each user access to the mysql tables may change some numerical values in the database which should be effected to the next user. For example lets take share market. Many users buy or sell a PLC's share at a point of time. and the share value will be reflected in the next user who is selling or buying.

So i want to lock unlock the database at a point of time while sql exection is on. And the other users who are accesing the database should wait until execution of the user finishes.


The users here are not of mysql. They are the users of the site.

Please help me with this. Do i need to change any thing in the code or settings in PHP or MYSQL???

And are there any automation tools by which i can test this?

Re: How to lock,unlock MYSQL tables?

Posted: Fri Jul 17, 2009 3:14 am
by Benjamin

Code: Select all

 
LOCK TABLE table_name WRITE;
-- execute queries here
UNLOCK TABLES;
 

Re: How to lock,unlock MYSQL tables?

Posted: Fri Jul 17, 2009 3:15 am
by onion2k

Re: How to lock,unlock MYSQL tables?

Posted: Fri Jul 17, 2009 3:22 am
by adisrikanth
thanks a lot for the replies...

one more question...so this LOCK statement makes other sessions to wait until it unlocks the tables or just kills the other session's queries???

Re: How to lock,unlock MYSQL tables?

Posted: Fri Jul 17, 2009 3:56 am
by onion2k
adisrikanth wrote:one more question...so this LOCK statement makes other sessions to wait until it unlocks the tables or just kills the other session's queries???
It locks the table so no other database connection can access them. If another script tries it'll wait until it can - or it'll time out if it waits too long.

Re: How to lock,unlock MYSQL tables?

Posted: Mon Jul 20, 2009 9:58 am
by adisrikanth
ok..thanks a lot..it worked....i even tested it....

Re: How to lock,unlock MYSQL tables?

Posted: Mon Jul 20, 2009 10:06 am
by Eran
Unfurtunately, this will not help your cause. Operations in well structured database occur in numerous milliseconds, much faster than web UI can update itself (due to network latency at the very least). If someone updates a value in your database other users will not see the changes immediately even though the table was locked and then unlocked for a very short period.

The solution is to check if the original value is still the same in the database as it appears in the UI and return an error message in the case it doesn't. The check and update should happen in a transaction which should remove the need to lock / unlock tables altogether.