Mysql innodb transaction concurrency?

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.

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. dev.mysql.com/doc/refman/5.1/en/lock-tab....

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 Gopal T Aug 2 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 2 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 Gopal T Aug 2 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 2 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 Gopal T Aug 2 at 16:43.

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.

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 Gopal T Aug 3 at 15:48 Please suggest me if you have better Idea. Thanks for your time. – Venu Gopal T Aug 3 at 15:48.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.