logoalt Hacker News

soontimes • today at 4:16 AM • 1 reply • view on HN

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

Replies

bijowo1676 • today at 4:24 AM

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
➕ show 2 replies