记一次Mysql并发更新处理

11月 19, 2018

记一次Mysql并发更新处理

大致流程:

  1. 查询

    1.1 无数据 INSERT INTO ON DUPLICATE KEY

    1.2 有数据

    1.2.1 未过期

    update set expire = date_add(expire,1年) where expire > 当前时间
    

    1.2.2 已过期

    update set expire = date_add(当前时间,1年) where expire < 当前时间
    
    1. 失败重试,一般只会出现 1.2.2失败的情况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

// UserPermissionsAdd 更新用户权限
func (s *Mysql) UserPermissionsAdd(ctx context.Context, in *proto.UserPermissionsAddArgs) (err error) {

var (
oldExpire int64

info = &proto.UserPermissions{}
now = time.Now()
db = models.Model(ModelConfigName.Write)
)

// 查询是否已购买过
if err := db.Where("uid = ? and category = ? and res_name = ? and res_id = ?", in.Uid, in.Category, in.ResName, in.ResId).First(info).Error; err != nil && err != gorm.ErrRecordNotFound {
return err
}

// 事务开始
tx := db.Begin()

switch {

case info.Id == 0: // 记录不存在的情况添加 set expire = now() + add_unix on duplicate key update expire = expire + add_unix

oldExpire = now.Unix()

err = tx.Exec("INSERT INTO user_permissions (uid,category,res_name,res_id,expire,role,created_at,updated_at) Values (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE expire = expire + ?, updated_at = ?", in.Uid, in.Category, in.ResName, in.ResId, now.Unix()+in.AddUnix, in.Role, now.Format("2006-01-02 15:04:05"), now.Format("2006-01-02 15:04:05"), in.AddUnix, now.Format("2006-01-02 15:04:05")).Error

case info.Id > 0 && info.Expire >= now.Unix(): // 未过期 set expire = expire + add_unix where expire > now()

oldExpire = info.Expire

err = tx.Where("uid = ? and category = ? and res_name = ? and res_id = ? and expire > ?", in.Uid, in.Category, in.ResName, in.ResId, now.Unix()).Update("expire", gorm.Expr("expire + ?", in.AddUnix)).Error

case info.Id > 0 && info.Expire < now.Unix(): // 已过期 set expire = now + add_unix wherer expire < now()

oldExpire = now.Unix()

err = tx.Where("uid = ? and category = ? and res_name = ? and res_id = ? and expire < ?", in.Uid, in.Category, in.ResName, in.ResId, now.Unix()).Update("expire", now.Unix()+in.AddUnix).Error

}

if err != nil {
tx.Rollback()
return err
}

// 写入log
logInfo := &proto.UserPermissionsLog{
Uid: in.Uid,
Category: in.Category,
ResName: in.ResName,
ResId: in.ResId,
OldExpired: oldExpire,
NewExpired: oldExpire + in.AddUnix, // 并发时不一定准,供参考
AddUnix: in.AddUnix,
OrderId: in.OrderId,
Origin: in.Origin,
OriginId: in.OriginId,
CreatedAt: now.Format("2006-01-02 15:04:05"),
UpdatedAt: now.Format("2006-01-02 15:04:05"),
}

// 添加log失败,说明已添加过这个order权限,回滚
if err := tx.Create(logInfo).Error; err != nil {
tx.Rollback()
return err
}

if err := tx.Commit().Error; err != nil {
return err
}

return nil
}