I have created a booking system and have to prevent double allocaiton of seats in a multi user environment. My main problem arises if more then 1 user wanted to book the exact same seat at the same time. How would I go about preventing this? Is there a way of temporarily locking a database for a user? All help appreciated.
There two different ways to lock with mysql, table or row and application level locks. With myISAM you can lock the table, with InnoDB you can just lock a row. The second way is do use an application level locking by requesting a named lock from mysql. My suggestion is to read yourself into this stuff carefully before making a decission if the application is worth it.
You most likely want to implement an application level lock. It will be easier to implement and much easier to maintain accountability of all locked entries. Something like creating a temp record in a locked_tickets table with a 5 minute expiration date. When a new user searches, you would exclude records in the temp table.
you can apply the row level lock and the table level lock,update lock when you apply the row level lock the row is not updated by the other but they access the table in the table level lock the lock is apply to the table so that any one can access the table but not update until the other user commit their transaction and release the table