3 ms·

Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have

Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty

bijowo1676 2mo ago
there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: with current_order as ( select $SKU1, $q2 as quantity union select $SKU2, $q2 as quantity ), with carts as ( select sku, sum(quantity) as reserved from active_carts group by sku ), with warehouse as ( select sku, available_units from inventory group by sku ) select * from current_order inner join carts using (sku) inner join warehouse using (sku) where warehouse.available_units - carts.reserved < current_order.quantity assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables
- soontimes 2mo ago
  I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
  - bijowo1676 2mo ago
    how does current design resolve concurrent actors fighting for the last item ? there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items. my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items. but the problem as formulated, imho, is not even correctly defined. Shopify incorrectly formulated the very problem they are trying to solve. Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout. the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay. So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY
    - soontimes 2mo ago
      > how does current design resolve concurrent actors fighting for the last item ? It resolves with skip locked. Assuming we have only 1 item left. First query scans the buffer table, locks as many rows as needed (1 in our case), and moves rows to another table. Second query scans the table, finds no rows (even if first one hasn’t finished yet, the row is locked and ignored), checks if it can increase buffer, finds out that it’s fully sold and aborts. Db guarantees that you can’t oversold. > my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. I can’t evaluate whether it’s the same or not, because you still haven’t clarified when exactly you’re going to insert the row. In the article they’re inserting in the same transaction. Would you also do it in the transaction? Because if you’ll introduce a separate global mechanism to resolve conflicts, on a high level it would be the same as their approach with redis (you need to have 2 systems) EDIT: wording
      - bijowo1676 2mo ago
        think about for a moment what that skip locked actually means, all these 1000 rows per SKU are logically equivalent to a Inventory table with a single row where available_units=1000 per SKU. now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ? shopify's design relies on DB to lock rows for transaction as a way to "decrement the counter" of available units. What I am suggesting, is you can just decrement counter by updating a single row, no need to lock 900 rows. Shopify moved from one extreme (single global variable in redis) to another extreme (1000 rows in db) and forgot about the middle ground. The dance with moving rows per each item between tables is completely unnecessary, it's like counting numbers one by one in a for loop, when you can just substract number directly. if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db: 1. Have a table with active shopping carts (cart_id, cart_status, sku, quantity) 2. when cart_status changes to 'Checkout' run inventory availability check 3. If inventory availability check fails, show error to user (before he clicks Pay) and suggest replacement items. 4. If inventory availability succeeds, proceed to charge cc availability check is the SQL above: inventory-sum(active_carts.quantity)-current_order must be > 0
- edoceo 2mo ago
  Thanks! I don't uSe 'with' enough
- codedokode 2mo ago
  The item is reserved when the user decides to place an order, but before paying for it. Not when a product is added to the cart because the user can keep it there for a month and end up not buying. You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units. That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy. Interestingly, MySQL docs has the documentation page with a similar case: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-lo...

bijowo1676 2mo ago

there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation the check for oversold items is extremely cheap: with current_order as ( select $SKU1, $q2 as quantity union select $SKU2, $q2 as quantity ), with carts as ( select sku, sum(quantity) as reserved from active_carts group by sku ), with warehouse as ( select sku, available_units from inventory group by sku ) select * from current_order inner join carts using (sku) inner join warehouse using (sku) where warehouse.available_units - carts.reserved < current_order.quantity assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables

soontimes 2mo ago
I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
- bijowo1676 2mo ago
  how does current design resolve concurrent actors fighting for the last item ? there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items. my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items. but the problem as formulated, imho, is not even correctly defined. Shopify incorrectly formulated the very problem they are trying to solve. Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout. the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay. So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY
  - soontimes 2mo ago
    > how does current design resolve concurrent actors fighting for the last item ? It resolves with skip locked. Assuming we have only 1 item left. First query scans the buffer table, locks as many rows as needed (1 in our case), and moves rows to another table. Second query scans the table, finds no rows (even if first one hasn’t finished yet, the row is locked and ignored), checks if it can increase buffer, finds out that it’s fully sold and aborts. Db guarantees that you can’t oversold. > my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process. I can’t evaluate whether it’s the same or not, because you still haven’t clarified when exactly you’re going to insert the row. In the article they’re inserting in the same transaction. Would you also do it in the transaction? Because if you’ll introduce a separate global mechanism to resolve conflicts, on a high level it would be the same as their approach with redis (you need to have 2 systems) EDIT: wording
    - bijowo1676 2mo ago
      think about for a moment what that skip locked actually means, all these 1000 rows per SKU are logically equivalent to a Inventory table with a single row where available_units=1000 per SKU. now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ? shopify's design relies on DB to lock rows for transaction as a way to "decrement the counter" of available units. What I am suggesting, is you can just decrement counter by updating a single row, no need to lock 900 rows. Shopify moved from one extreme (single global variable in redis) to another extreme (1000 rows in db) and forgot about the middle ground. The dance with moving rows per each item between tables is completely unnecessary, it's like counting numbers one by one in a for loop, when you can just substract number directly. if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db: 1. Have a table with active shopping carts (cart_id, cart_status, sku, quantity) 2. when cart_status changes to 'Checkout' run inventory availability check 3. If inventory availability check fails, show error to user (before he clicks Pay) and suggest replacement items. 4. If inventory availability succeeds, proceed to charge cc availability check is the SQL above: inventory-sum(active_carts.quantity)-current_order must be > 0
edoceo 2mo ago
Thanks! I don't uSe 'with' enough
codedokode 2mo ago
The item is reserved when the user decides to place an order, but before paying for it. Not when a product is added to the cart because the user can keep it there for a month and end up not buying. You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units. That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy. Interestingly, MySQL docs has the documentation page with a similar case: https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-lo...