How to do stock reduction for a list of items in shopping cart using distributed lock and SQL transaction to ensure consistency

Question

Question:

• I wrote a Sell() function which do stock reduction for a list goods in user's shopping cart. Assume there will be thousands of request within a second for those goods. Since we need to improve the throughput and make sure the service is not down under pressure or network error, etc. We deploy the inventory service in multiple servers. Here we have one database for inventory service.

1. We need transaction as we have a list of goods which in process, if one of the goods failed to do the stock reduction, we have to rollback.
2. We need a Redis distributed lock to make sure data consistency. Only one thread/routine will access/change the information of a specific goods at a time.

Since we need to place the loop inside between start transaction and commit() to ensure the property of transaction. We have to lock before transaction and unlock after commit. But this will make transaction Serialized. I am trying to use a fine-grained Redis lock which is basically every goods generates a lock using its ID. Clearly my code is wrong. I know there could be other options like optimistic lock Pessimistic Lock. But How do I implement this idea with Redis lock?

I simplified it as follows:

// assume no lock or unlock error, stock is enough, goodsID exist

tx := DB.Begin() // begin transaction

loop a list of goods in shop cart
for _, good = range [] goods { 

      mutex := GetRedisLock(good.ID) // for each item, get a distributed Redis lock by its name
      mutex.lock()
      inv := DB.Getby(goodID) // get inventory by goodID
      inv.Stock -= good.ReductionNum  // do stock reduction for this item
      tx.Save(inv) // save current value to database
      mutex.Unlock() 
}
tx.Commit()

demo code in go:

func (*InventoryServer) Sell(ctx context.Context, req *proto.SellInfo) (*emptypb.Empty, error) {

   client := goredislib.NewClient(&goredislib.Options{
      Addr: "localhost:6379",
   })
   pool := goredis.NewPool(client) 

   rs := redsync.New(pool)

   // start transaction
   tx := global.DB.Begin()
   for _, goodInfo := range req.GoodsInfo {
      var inv model.Inventory
     
     // create a new distributed lock for each goodID which needs to do reduction
      mutex := rs.NewMutex(fmt.Sprintf("goodsID:%d", goodInfo.GoodsId))
      if err := mutex.Lock(); err != nil {
         return nil, status.Errorf(codes.Internal, "get redis lock error")
      }

      if result := global.DB.Where(&model.Inventory{Goods: goodInfo.GoodsId}).First(&inv); result.RowsAffected == 0 {
         tx.Rollback()
         return nil, status.Errorf(codes.NotFound, "no stock info")
      }
      if inv.Stocks < goodInfo.Num {
         tx.Rollback()
         return nil, status.Errorf(codes.ResourceExhausted, "not enough stock")
      }
      inv.Stocks -= goodInfo.Num
      tx.Save(&inv)

      if ok, err := mutex.Unlock(); !ok || err != nil {
         return nil, status.Errorf(codes.Internal, "release redis lock error")
      }
   }
  
  
   tx.Commit()
   return &emptypb.Empty{}, nil
}