[ How to handle MySQL locks? ]
My requirement is, users can click and avail the deal. One deal can be availed by only one member, so I'm locking the table when a user tries to avail a deal, and unlocking it. So that if two users clicks and tries to avail the deal, it will form a queue and it will prevent two users to avail the deal.
The code is like
LOCK TABLES deal WRITE;
//MySQL queries and my php code goes here.
The problem now is, what if some problem happens with my php code between lock and unlock, will the table get locked permanently? Is there anyway i can set a maximum time to lock the table?
If I would have been in your place I would have created a database table named locked_deals. It would have a column named deal_id. When ever a user chooses a deal, its deal_id will get inserted into the locked_deals table. And when the next user clicks on the same deal it will first check if the deal_id is in lock table. If yes it would not allow the user to choose the deal. Finally when everything goes fine we can delete the lock_id from the lock table at the end of process. For the lock_ids which get stuck into the table because of any exception in the php code - we can create a background service that cleans the stucked ids (which ahve been stuck for the last n minutes) in the locked_deals table every n minutes. Hope it helps.
If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session.