#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Lock tables and/or transaction for stock table?


    How can I maintain data integrity for a stock table with InnoDB? Does this require the use of both a table lock and a transaction? I have tried to find some good examples, but even well known opensource ecommerce software doesn't implement table locks/transactions. Would below code work? Do I really need to lock the whole table, or is there a way to only lock the rows of the sold products? Or does anyone have a good example?

    Code:
    CREATE TABLE `stock` (
    `product_id` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `quantity` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`product_id`)
    )
    COLLATE='utf8mb4_unicode_ci'
    ENGINE=InnoDB;
    Code:
    LOCK TABLES stock WRITE;
    START TRANSACTION;
    SET autocommit = 0;
    UPDATE stock SET quantity = quantity - 4 WHERE product_id = 'PRODUCT_1' AND quantity >= 4;
    UPDATE stock SET quantity = quantity - 2 WHERE product_id = 'PRODUCT_2' AND quantity >= 2;
    UPDATE stock SET quantity = quantity - 5 WHERE product_id = 'PRODUCT_3' AND quantity >= 5;
    COMMIT or ROLLBACK;
    COMMIT if all UPDATE queries have 1 affected row, otherwise ROLLBACK
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    106
    Rep Power
    65
    How can I maintain data integrity
    By not using an UPDATE query at all. By trying to maintain a value through UPDATE queries, you have no audit-trail. You will never know if a programming error, hacking, or something as simple as a duplicate form submission has incorrectly modified the value.

    You need to insert a new record for each transaction that contains either a positive or negative quantity for a product_id. To get the total quantity at any point in time, just GROUP BY the product_id and use SUM(quantity) in the SELECT term. The row(s) you insert should contain all the appropriate Who, What, When, Where, and Why information about the transaction.

IMN logo majestic logo threadwatch logo seochat tools logo