PHP，MySQL中的数据阻塞

Question

I'm writing a Queue Management System for a small clinic. There will be multiple users trying to do same thing, so these is a concurrency problem. I'm familiar with ACID guarantee and also understand notion of transaction. I know that two people can not change same data at the same time.

But here's my problem: I have a PHP function isFree($time) which determines if particular doctor is free for that time. I'm afraid that if both users try to call same function, both of them may get positive result and mess things up, so somehow I need to either queue concurrent users, or accept only one.

Easiest way to solve this problem would be to restrict, that my function can be called one at a time. I probably need some kind of flag or blocking system, but I have no exact idea on how to do it.

Or on the other hand, It would be even faster to only restrict those function calls, which may overlap. For example calling isFree($time) function for Monday and Tuesday at the same time won't cause any problems.

Answer 1

You're effectively asking for a lock.

I am guessing your queue system runs on MySQL for databases. if so, you can LOCK the table you're using (or on some database engines, the specific row you are using!). The structure is LOCK TABLES yourTableName READ.

This will effectively prevent anyone else from reading anything in the table until:

1. Your session is ended
2. You free the lock (using UNLOCK)

This is true for all database storage engines. InnoDB supports row-level locking through transactions. Instead of using a SELECT query, suffix it with FOR UPDATE to get a complete lock over the row(s) you just read.

This will hopefully shed more light on the locking mechanism of MySQL/innoDB. In order to free the lock, either UPDATE the row or commit the transaction.