www.webdeveloper.com
Results 1 to 9 of 9

Thread: MySQL Data concurrency

Hybrid View

  1. #1
    Join Date
    Mar 2009
    Posts
    452

    MySQL Data concurrency

    hi all,
    Consider the following senario:
    Salesman 1 sells three shirts
    Salesman 1 retrieves current shirt count (47):
    SELECT quantity FROM inventory WHERE item='shirt'
    Salesman 2 sells two shirts
    Salesman 2 retrieves current shirt count (47):
    SELECT quantity FROM inventory WHERE item='shirt'
    Salesman 1 computes new inventory level: 47 - 3 = 44 and updates shirt count:
    UPDATE inventory SET quantity = 44 WHERE item = 'shirt'
    Salesman 2 computes new inventory level: 47 - 2 = 45 and updates shirt count:
    UPDATE inventory SET quantity = 45 WHERE item = 'shirt'
    At the end of this sequence, five shirts are sold (that's good), but the inventory level says 45 rather than 42 (that's bad).
    The problem arises when two or more SQL statements are dependent.

    any ideas ?

  2. #2
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    Hmmm.

    Would you believe it that the scenario you described is worse compounded in a distributed computing environmnt (DCE)?

    Any further ideas about solutions from these classes of problems where real solutions cost exorbitanty large doller amounts? The cost is somewhere around USD .5M.

  3. #3
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    UPDATE inventory SET quantity = quantity -3 WHERE item = 'shirt'

    UPDATE inventory SET quantity = quantity -2 WHERE item = 'shirt'

  4. #4
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    I doubt it.

    Quote Originally Posted by Dasher View Post
    UPDATE inventory SET quantity = quantity -3 WHERE item = 'shirt'

    UPDATE inventory SET quantity = quantity -2 WHERE item = 'shirt'
    Your results will be corrupted when quantities are low. You will be selling shirts you do not have!

  5. #5
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    "Your results will be corrupted when quantities are low. You will be selling shirts you do not have! "

    I did not show a complete solution, only a solution to the problem the OP had.

  6. #6
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    This sort of thing is really best handled with SQL transactions. If you're running SQL Server, you should be set. If you're running MySQL, I think you'll need to be using the InnoDB engine (unless MyISAM has been updated since last I used it).

    When a salesman pulls up the shirt inventory, you read it as-is. But, when the salesmen enters the sale quantity, you begin a transaction:

    Code:
    start transaction;
    select quantity from inventory where item='shirt';
    Calculate new_quantity. If new_quantity >= 0:

    Code:
    update inventory set quantity={$new_quantity} where item='shirt';
    commit;
    Else if new_quantity < 0:

    rollback;
    The rollback in our case doesn't really do anything, since we haven't changed anything. But, it lets SQL Server / MySQL know that it can unlock rows from table inventory were item='shirt', and it ensures that nothing is changed. (If your transaction grows in complexity, you don't want to have a neglected commit where you shouldn't!)
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  7. #7
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    The thing to be aware of, and perhaps it's obvious, is that once you begin a transaction and query inventory for rows where item='shirt', no one else can touch those rows until the transaction is committed or rolled back. So, it's important that you run an isolated, non-transaction query to pull the quantity for display first. Then, when you actually update the quantity, you need to ensure the transaction isn't waiting on user input to complete.

    Everything between start transaction and commit/rollback needs to happen as quickly as possible.
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  8. #8
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    Assuming that quantity is actual quantity of shirts available to sell, and the new SET value is quantity minus those actually sold, it should not be possible for the value to go negative unless the quantity value was inaccurate in the first place. Quantity should = stock on hand. (or at all stores or whatever)

    Another option may be to use

    LOCK TABLES

    UPDATE inventory SET quantity = quantity - x WHERE item = 'shirt'
    or
    SELECT quantity ... and
    UPDATE quantity SET = x ....

    UNLOCK TABLES

  9. #9
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Quote Originally Posted by Dasher View Post
    Assuming that quantity is actual quantity of shirts available to sell, and the new SET value is quantity minus those actually sold, it should not be possible for the value to go negative unless the quantity value was inaccurate in the first place. Quantity should = stock on hand. (or at all stores or whatever)

    Another option may be to use

    LOCK TABLES

    UPDATE inventory SET quantity = quantity - x WHERE item = 'shirt'
    or
    SELECT quantity ... and
    UPDATE quantity SET = x ....

    UNLOCK TABLES
    Lock tables should only be used for operations that will affect all or most of a table or set of tables. No sense blocking other transactions that have nothing to do with the rows you're working with
    Jon Wire

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles