How to deal with tons of simultaneous UPDATE request to one record in mySQL database and determine who arrived first

I have designed a website displaying books for rent using php and mySQL. When someone click on the book, an information page with countdown timer will be displayed and the user has 10 seconds to decide whether he want to borrow it. If the time is up, user need to go back and click on the book again to display the "borrow page". When a user click on the book, PHP actually check first if the book is currently being "viewed" by someone else. If nobody is viewing it, PHP display the "borrow page" and the user gain 10secs exclusivity to the book. During this 10 secs, other users wont be able to open the page or view the "borrow button". Everything is up and running except I don't know how to do it properly so only exactly one user can get the 10 secs exclusivity first. My logic is as follows:

MySQL table:

Book(book_ID, title, current_user, expire_time)

a. When user click on book: PHP Check if expire_time < NOW()

b. If true (previous viewing time expired), set the current_us开发者_如何转开发er field to requester's userID and set the expire time to now + 10 secs:

UPDATE Book SET current_user='$userID', expire_time=ADDTIME(NOW(), '00:00:10')

c. If false (still within viewing time), further check if current_user field equals requester's userID.

d. if true, display the "borrow page".

e. if false, display error msg saying "someone else is currently viewing the book. please come back in 10 secs"

I have tested clicking the book simultaneously using 2 computers. Most of the time, only one computer can access the page and gain exclusivity for 10 seconds. However, there was one time that both computers successfully opened the "borrow book" page at the same time. This is definitely not what I want. Imagine if there are 100 web request asking to open the same book and half of them go through while there is only 1 book available for rent.

The books borrow thing are just for illustration purpose. What I want to know is, when there are tons of query arrive at mysql server at the same time, how to get or choose the first one and put the rest on hold?

---

I would encapsulate the locking into a class, then you can do this:

$book = new Book($book_id);
$success = $book->getLock($user_id);
if (!$success)
  echo 'Lock failed';
else
  echo 'You got 10 seconds to buy, sucka!';


    class book {
        private $id = false;
        private $initialized = false; 
        public function __construct($id = false) {
            if (!is_numeric($id) || $id === false)
                return false;
                    $this->id = $id;
            $this->initialized = true;
            return;
        }
        public function getLock($userID = false) {
            if ($this->initialized !== true)
                return false;

            if (!is_numeric($userID) || $userID === false)
                return false;

           // check for a current lock
           $sql = 'SELECT current_user, expire_time FROM Book WHERE id = '.$this->id;

           // do whatever you do, get back the row
           $avail = do_query($sql);

          // the book is already locked
          if ($avail['current_user'] > 0 && strtotime($avail['expire_time']) > time())
        return false;

            // assert the lock
            $sql = 'UPDATE Book SET current_user='.$userID.', expire_time=ADDTIME(NOW(), "00:00:10") WHERE id = '.$this->id;

            // use whatever method you use for db access
            do_query($sql);

            // verify the lock
            $sql = 'SELECT current_user FROM Book WHERE id = '.$this->id;

            // do whatever you do for db access, get the id field
            $result = do_query($sql);

            // if these match, lock was a success
            if ($result == $userID)
                return true;

            return false;
        }
        public function releaseLock($userID=false) {
            if ($this->initialized !== true)
                return false;

            if (!is_numeric($userID) || $userID === false)
                return false;

            // verify the lock is in the specified user's id
            $sql = 'SELECT current_user FROM Book WHERE id = '.$this->id;

            // do whatever you do for db access, get the id field
            $result = do_query($sql);

            // if these match, lock is actively assigned to this user
            if ($result == $userID)
                return false;

            // release it
            $sql = 'UPDATE Book SET current_user=NULL, expire_time=NULL WHERE id = '.$this->id;
            do_query($sql);
            return true;
        }

    }

---

You need to add checking for the "book already being taken" to your UPDATE statement. I.e. in your WHERE clause in addition to the ID also check that the expire_time has run out. After the UPDATE, you need to read the record back to check if you successfully "reserved" it. This way, only one of your updates passes.