TAGS :Viewed: 7 - Published at: a few seconds ago

[ 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.
UNLOCK TABLES;

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?

Answer 1


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.

Answer 2


If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session.

Source