multiple transactions

[adolf23]

I have an application that assigns room numbers to student when they are registered.
If one person is using the software to register students it works perfectly but when multiple people are using the software through peer to peer network it sometimes give problems by assigning the same room number to multiple students while it should not work like that. Each student should have unique room number from the database. My database is mysql.

[pritaeas]

Make sure your table does not accept the same roomnumber twice by making a unique index for it.

[adolf23]

Someone advise me to use table locks.
That is if one client make request to access the table it would be in lock mode which will prevent any other client from accessing it until the system has finish handling the request before the lock would be released for another client to also access it.

[pritaeas]

I think it is a bit overkill if you just want to assure that the roomnumber is not used twice, but perhaps your table structure requires locks, can't really say without knowing the structure.

[adolf23]

Thanks a lot for all the replies.
What I want to do is that when a client is making a request the system
should not process request from any other client until the first request has been processed before its switches to another request.

[pritaeas]

http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html (use at your own risk, I do not recommend this)

[adolf23]

please what is the downside of table locks.
Thank you

[pritaeas]

Why lock a table, if in most cases there will be no duplicate items selected. What if something crashes, how will you release the lock? How will you queue all clients waiting for a result, and what when they time out?

[adolf23]

Thank you very much