4

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!

Community
  • 1
  • 1
Venu
  • 7,243
  • 4
  • 39
  • 54
  • You want this http://stackoverflow.com/questions/11278494/mysql-atomic-operations-and-table-locking, particularly the first answer. Basically you need to consolidate everything that needs to be checked in one row of one table and then protect all operations on that with locks. – Gordon Wrigley Nov 21 '12 at 12:01

2 Answers2

1

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

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • Thanks to your replay Dan. Can we apply locking just for writing? So that request came for just reading wouldn't wait to release lock. If not could you suggest other way of achieving this? – Venu Aug 02 '11 at 15:50
  • But looking up how much inventory is still available is a read! Such a lock is possible (you are here replying to a link to the page explaining the lock types) but would not serve your purpose; it would allow overselling which is what you're trying to avoid. – Dan Grossman Aug 02 '11 at 15:54
  • hmm right Dan. But I have to show how much inventory is still available while showing product info(even though it might be wrong). I am validating again when user clicks buy button; Then I am storing it in reservations table and allowing some time(8mins) to users to finish transaction. If they didn't complete transaction, will remove record through cron job. So users viewing product info shouldn't wait for lock to release. Please suggest me if I am wrong. – Venu Aug 02 '11 at 16:00
  • You only place the lock when you're actually going to modify the table to avoid the race condition where one connection is trying to lower the quantity and another reads it before that happens. That's the read you want to block. The table will be locked for as long as it takes to INSERT the row in your reservations table (milliseconds), then you unlock it and any other connections can make their read and see the higher reserved quantity. You don't leave it open for 8 minutes, that's not what we're talking about. – Dan Grossman Aug 02 '11 at 16:25
  • yeah I got it now. I agree I have to lock table. Btw, I assume reads will be of 80% and writes will be 20%. So 80% of reads to the table has to wait for those milliseconds. That is what I am concerning about. Re 8mins, Its payment session for an user. (Yeah its unrelated to our topic, I have explained you to give you a clear picture on what I am trying to do). Since it is a eCommerce app, users might be heavy. Will it slow the app? Thanks for your time. – Venu Aug 02 '11 at 16:43
  • Dan, I have updated my question a bit. Now I understood, I have to use locking. But have some doubts. Please clarify those. Thanks to your valuable time. – Venu Aug 03 '11 at 07:16
  • Dan, There seems to be multiple issues with locks inside transactions.. I checked this one. http://dev.mysql.com/doc/refman/5.1/en/lock-tables-and-transactions.html.. Can I use select for update in my case? Please suggest me. – Venu Aug 09 '11 at 13:20
1

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.

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
  • Admin can set how many products can sell.. When user selects number of quantity and clicks buy will take him to payment page. What if he paid money to site, but products were sold out in meantime? System has to refund money right? I feel this would make sense,When user clicks pay button, I am storing it in reservations table and allowing some time(8mins) to users to finish transaction. If they didn't complete transaction, will remove record through cron job. If they complete transaction, will move record from reservations to purchases. At any time items remaining = purchases + reservations. – Venu Aug 03 '11 at 15:48
  • Please suggest me if you have better Idea. Thanks for your time. – Venu Aug 03 '11 at 15:48