mysql innodb transaction concurrency

I have 3 tables

Products

product_id

max_products_can_sell

max_products_can_sell_to_individual

purchases

user_id

product_id

quantity

reservations

product_id

user_id

quantity

Hope you understood the structure.

Now, When ever user is trying to purchase a product, I have to check the items remaining by max_products_can_sell - (quantity sold + quantity reserved).

If items are available I have to store it in reservations table until he purchases. (Will do house keeping through cron job for this table)

Now Actual question is, How to handle concurrency issues here. eg: pen product have 1 only. two users request reserve.php.

"a" user request reserve.php and available pen is 1 . but before insert, for "b" user available pen is 1 yet. so two users reserve pen.

I am using innodb table. How to handle it?

EIDT

  $query = SELECT count(*) as sold     FROM purchases WHERE product_id = 1;    
    $query = SELECT count(*) as reserved FROM reservations WHERE product_id = 1;     
    $items_remaining = $sold+$reserved;       

    if ($items_remaining) {      
       //INSERT data to reservations    
    } 

Now, I need to ensure that no other queries will interfere and perform the same SELECT (reading the 'old value' before that connection finishes updating the row.

As Dan told, I can LOCK TABLES table to just make sure that only 1 connection is doing this at a time, and unlock it when I'm done, but that seems like overkill. Would wrapping that in a transaction do the same thing (ensuring no other connection attempts the same process while another is still processing)? Or would a SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE be better?

I'm confused whether to use transactions vs locking tables or use both. Please suggest me开发者_运维问答..

EDIT2

In products table, there is another field called max_can_sell_to_individual. I have to check the current inventory and also have to check the personal limit.

I can maintain inventory(stock available) but I have to check the individual limit also. That can be found from purchases table.

Please suggest me How can I handle it?

Thanks in advance!

I would lock only the product record (with select for update - note, select lock in share mode won't block other user to buy the same product) and then perform the rest of the operation. In this way I won't block purchasing of other products (while locking the table will block any write operations, regardless if there are for product 1 or product 2) And why you are keeping max_product_can_sell property instead of (or not along with) available_quantity property?

As Dan told, I can LOCK TABLES table to just make sure that only 1 connection is doing this at a time, and unlock it when I'm done, but that seems like overkill. Would wrapping that in a transaction do the same thing (ensuring no other connection attempts the same process while another is still processing)?

Depends on isolation level. In serialisable - yes, in lower levels, I'm almost sure, no.

Lock the table, perform your transaction, unlock the table. Any requests that come in while the transaction is open will get held waiting for the lock to be released.

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html