MySQL Forums :: Transactions :: mysql innodb transaction concurrency
Re: mysql innodb transaction concurrency
Posted by: Rick James ()
Date: August 14, 2011 11:59AM
Do NOT use InnoDB's locking to handle long-running transactions.
8 minutes is LONG!
8 seconds is maybe OK.
Keep your reservation system!
An item has 3 states:
* In inventory, available for someone to purchase;
* Grabbed by someone, but awaiting confirmed purchase; others are not allowed to initiate a purchase on this item; (You could provide the info "this items is 'tentatively' sold.)
* Purchased.
Alternatively, you could design it so an item goes through these 2-3 steps:
* In inventory, available for someone to purchase;
* Purchased;
* If they fail to purchase (or 8 minutes passes), then "put it back into inventory"
Either scheme would use InnoDB transactions _within_ each of the three steps. This would assure consistency at the low level. You have to assure consistency at the high level (via the three steps).
Furthermore (assuming this is web-based)...
A web page is "stateless". You cannot carry a MySQL connection from one page to the next. So, you cannot keep an InnoDB transaction open.
Furthermore...
Do you really want to lock out other users for 8 minutes? 1 second is long enough!
8 minutes is LONG!
8 seconds is maybe OK.
Keep your reservation system!
An item has 3 states:
* In inventory, available for someone to purchase;
* Grabbed by someone, but awaiting confirmed purchase; others are not allowed to initiate a purchase on this item; (You could provide the info "this items is 'tentatively' sold.)
* Purchased.
Alternatively, you could design it so an item goes through these 2-3 steps:
* In inventory, available for someone to purchase;
* Purchased;
* If they fail to purchase (or 8 minutes passes), then "put it back into inventory"
Either scheme would use InnoDB transactions _within_ each of the three steps. This would assure consistency at the low level. You have to assure consistency at the high level (via the three steps).
Furthermore (assuming this is web-based)...
A web page is "stateless". You cannot carry a MySQL connection from one page to the next. So, you cannot keep an InnoDB transaction open.
Furthermore...
Do you really want to lock out other users for 8 minutes? 1 second is long enough!
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.
© 2013, Oracle Corporation and/or its affiliates