Gorm

0. create table naming strategy

    DB, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        NamingStrategy: schema.NamingStrategy{
            //TablePrefix: "mxshop_",   // prefix
            SingularTable: true,　　　　　// table name not change to "xxxes"
        },
        Logger: newLogger,
    })

1. use pagination to query + count total 

    global.DB.Scopes(Paginate(int(req.Pages), int(req.PagePerNums))).Find(&brands)

    // total
    var total int64 global.DB.Model(&model.Brands{}).Count(&total)
    brandListResponse.Total = int32(total)

func Paginate(pg, pgSize int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        switch {
        case pgSize > 100:
            pgSize = 100
        case pgSize <= 0:
            pgSize = 10
        }
        offset := (pg - 1) * pgSize
        return db.Offset(offset).Limit(pgSize)
    }
}

2. 本地事务开启

// reduce inventory
// 本地事务保证 input[1:5, 2:10, 3:20]全部执行，不能保证多线程下数据一致
func (s *StocksServer) Sell(c context.Context, sell *proto.SellInfo) (*emptypb.Empty, error) {
    tx := global.DB.Begin() // start local transaction
    for _, g := range sell.GoodsInvInfo {
        var inv model.Inventory
        if result := global.DB.First(&inv, g.GoodsId); result.RowsAffected == 0 {
            tx.Rollback() // rollback
            return nil, status.Errorf(codes.InvalidArgument, "inventory not found")
        }
        if inv.Stocks < g.Num {
            tx.Rollback() // rollback
            return nil, status.Errorf(codes.ResourceExhausted, "inventory not enough")
        }
        inv.Stocks -= g.Num // 数据一致要靠分布式锁
        tx.Save(&inv) // 否则事务失败
    }
    tx.Commit() // commit
    return &emptypb.Empty{}, nil
}

数据库加悲观锁

// reduce inventory
// 本地事务保证 input[1:5, 2:10, 3:20]全部执行，不能保证多线程下数据一致
func (s *StocksServer) Sell(c context.Context, sell *proto.SellInfo) (*emptypb.Empty, error) {
    tx := global.DB.Begin() // start local transaction
    //l.Lock()
    for _, g := range sell.GoodsInvInfo {
        var inv model.Inventory
        if result := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
            Where(&model.Inventory{Goods: g.GoodsId}).First(&inv); result.RowsAffected == 0 {
            tx.Rollback() // rollback
            return nil, status.Errorf(codes.InvalidArgument, "inventory not found")
        }
        if inv.Stocks < g.Num {
            tx.Rollback() // rollback
            return nil, status.Errorf(codes.ResourceExhausted, "inventory not enough")
        }
        inv.Stocks -= g.Num // 数据一致要靠分布式锁
        tx.Save(&inv)
    }
    tx.Commit() // commit
    //l.Unlock()
    return &emptypb.Empty{}, nil
}

数据库加乐观锁

func (s *StocksServer) Sell(c context.Context, sell *proto.SellInfo) (*emptypb.Empty, error) {
    tx := global.DB.Begin() // start local transaction
    for _, g := range sell.GoodsInvInfo {
        var inv model.Inventory
        for {
            if result := global.DB.
                Where(&model.Inventory{Goods: g.GoodsId}).First(&inv); result.RowsAffected == 0 {
                tx.Rollback() // rollback
                return nil, status.Errorf(codes.InvalidArgument, "inventory not found")
            }
            if inv.Stocks < g.Num {
                tx.Rollback() // rollback
                return nil, status.Errorf(codes.ResourceExhausted, "inventory not enough")
            }
            //inv.Stocks -= g.Num // 数据一致要靠分布式锁
            // upate inventory where goods = inv.goodid, version = inv.version set stocks = inv.Stocks- g.num and version = inv.version+1
            if result := global.DB.Model(&model.Inventory{}).Select("stocks", "verson").  // GORM update zero value requires select()
                Where("goods = ? and verson = ?", inv.Goods, inv.Verson).
                Updates(model.Inventory{Stocks: inv.Stocks - g.Num, Verson: inv.Verson + 1}); 
            result.RowsAffected == 0 {
                zap.S().Info("reducing stocks failed")
            } else {
                break
            }
        }

    }
    tx.Commit() // commit
    return &emptypb.Empty{}, nil
}