How to lock,unlock MYSQL tables?

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
adisrikanth
Forum Newbie
Posts: 4
Joined: Fri Jul 17, 2009 2:37 am

How to lock,unlock MYSQL tables?

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How to lock,unlock MYSQL tables?

Post by Benjamin »

Code: Select all

 
LOCK TABLE table_name WRITE;
-- execute queries here
UNLOCK TABLES;
 
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to lock,unlock MYSQL tables?

Post by onion2k »

adisrikanth
Forum Newbie
Posts: 4
Joined: Fri Jul 17, 2009 2:37 am

Re: How to lock,unlock MYSQL tables?

Post 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???
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to lock,unlock MYSQL tables?

Post 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.
adisrikanth
Forum Newbie
Posts: 4
Joined: Fri Jul 17, 2009 2:37 am

Re: How to lock,unlock MYSQL tables?

Post by adisrikanth »

ok..thanks a lot..it worked....i even tested it....
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to lock,unlock MYSQL tables?

Post 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.
Post Reply