Concurrent users and updates on a record going out of control

[eluser]flaviog[/eluser]
I am working on a site that has a huge load of traffic just for a couple of seconds, 100's of users trying to buy a discounted low quantity product and simultaneous transactions occur almost exactly at the same time. Before I allow a transaction, I check the record for the available quantity, and if so, allow the transaction and update the available quantity with the lower number.

The problem is that I am tending to "oversell" since this very fast transactions occur simultaneously. On every case I check for availability, by the time I update the record, I am over the amount I wanted to sell. This is because vs transactions occur at the exact same time.

I tried different things and EVEN changed the host for a faster dedicated server to see if it was a hard problem. No luck

I am sure many of you have seen this problem on a high traffic site that has updates at the same time on the same record, but I haven't found how to solve this in CodeIgniter 1.7 nor anything related to locking records or handling this with some kind of queue.

Anyone knows how to solve this ? Thank you.