CODEWITHSUNDEEP
mysqltransactions
Chris Muench
Chris Muench

Reputation: 18318

Conflicting Transactions causes odd behavior

I have an application that records sales transactions. During this process, inventory amounts are updated and logged. These queries run as a transaction. But if 2 of these transactions run at once; the inventory count and logging can be incorrect.

Below is an example of queries. If they run at the same time the 2nd query might see the same value's that the 1st query saw resulting in only 1 of the transaction to update the quantity correctly. The transaction log will have both entries but the current quantity would be wrong if they run at the same time.

What is the best way to ensure that these transactions run in a way that won't cause inconsistencies

INSERT INTO transaction_log(item_id, current_quantity, amount_changed) VALUES (1, 10,-1);
INSERT INTO inventory (current_quantity, item_id) VALUES (10, 1) ON DUPLICATE KEY UPDATE quantity = 10

Upvotes: 0

Views: 60

Answers (1)

spencer7593
spencer7593

Reputation: 108370

in the update of inventory, we could apply an adjustment to the value, rather than specifying a replacement value

(we will assume that this question is not referring to an issue with the INSERT to the transaction_log table; adding rows to that table works fine. we will assume the issue is with the update of inventory table)

We are going to assume that current_quantity column referenced in the column list is quantity from the transaction, and the quantity column referenced in the update part is the running value.

For the sake of this example, I'm going to refer to these as quantity "adjustment" and "onhand".

Let's say the table row currently looks like this:

 item_id  quantity_onhand  quantity_adjust
 -------  ---------------  ---------------
       1              100               20

The current inventory onhand is 100, and the last adjustment that was applied is 20.

Let's say that we need to adjust the quantity_onhand by a value of 10. We could do something like this:

INSERT INTO inventory (item_id, quantity_adjust) VALUES (1, 10)
ON DUPLICATE KEY
UPDATE quantity_onhand = quantity_onhand + VALUES(quantity_adjust)
     , quantity_adjust =                   VALUES(quantity_adjust)

Assuming item_id is the primary key (or unique key) violated by the insert, we expect the result to be:

 item_id  quantity_onhand  quantity_adjust
 -------  ---------------  ---------------
       1              110               10

i.e. 10 is added to the "onhand" quantity, and 10 is stored as the last "adjust" value.

Note that in update portion of the statement, the VALUES() function returns the value that would have been inserted, if the insert had succeeded.

The result is effectively equivalent to executing this statement:

UPDATE inventory
   SET quantity_onhand = quantity_onhand + 10
     , quantity_adjust =                   10
 WHERE item_id = 1

Because the statement is supplying only an adjustment value, and not a specific onhand value i.e. getting the current inventory value from the row itself, at the time the row is locked by the statement, then this handles the concurrency issue with multiple statements trying to replace the onhand inventory amount.

Note that we also need to handle the case when the INSERT statement succeeds, when the row does not exist. We could define the quantity_onhand column as `DEFAULT 0 (to avoid storing a NULL value), then we would get a zero stored as the onhand amount, which is probably not what we want.

Upvotes: 1

Related Questions