数据库Update操作的一些技巧总结

数据库Update操作的一些技巧总结

问题背景：
在高并发场景下，当多个任务同时操作同一条数据表记录时，需要保证数据一致性。

1.带约束条件的update操作

场景举例：

预付款充值场景，假设用户创建一笔账单用于充值，在充值前，系统将账单状态status置位“待支付”，当系统在收到成功充值的通知后，会设置status为“成功支付”，并累计余额。但由于一些原因，系统可能会收到多条同样的充值成功消息。系统针对每条消息都设置状态，虽然不会对账单的status本身产生负面影响，但每次都成功处理后，必然会导致余额重复增加，这显然是系统不希望达到的预期（当然用户本身是乐意的，白赚了，^v^）。为此，系统需要保证即使在收到重复通知时，只成功设置一次账单的状态，由“待支付”迁为“成功支付”，从而保证只对余额增加一次。
解决方案：
一般情况下，我们会通过在业务层面先判断账单的status为“待支付”时，才update状态为“成功支付”，其他非“待支付”场景，业务直接返回错误，即：

if(billEntity.status != "待支付"){    return error;}else{    //调用update操作，修改status状态    //update t_bill set status = "成功支付" where bill_id = $id}

但在高并发场景下，如果两个操作同时判断出status状态都为”待支付”，则依然会执行多次成功的update操作。
为此，我们在数据库层面也增加保护措施，通过增加约束的方式来执行update操作。具体方案如下：

string sql = @"update t_bill set status = "成功支付"  where bill_id=$id and status = "待支付"";if( context.Dosql(sql) <= 0 ){    return error;}

在数据库中，当第二次执行update操作时，由于status在数据库中的值已经为“成功支付”，所以第二次update操作影响的记录个数会等于0，导致更新失败，从而保证了业务层面不会继续执行后续的逻辑。

2.增量update操作

场景举例：

  预付款充值场景，假设用户的余额初始为100元，现在通过两台设备同时充值50元，则在两笔充值都成功的情况下，用户的余额应该变为200元。假设采用不带约束的update方式进行余额修改，即如下的sql语句：

    update  t_balance  set balance = $newBalance

在并发操作时，如果以如下的顺序并发执行：

1. A查出初始余额为100元；
2. B查出初始余额为100元；
3. A改写余额为100+50元；

4. B改写余额为100+50元。

   很显然，由于第二次update改写操作直接覆盖了第一次的操作，导致最终的余额不符合预期。

解决方案：
这里我们可以根据增量更新的方式，每次update操作，不是将最终计算的结果先在业务层计算得出后写入数据库，而是将计算的逻辑放在数据库层去执行。于是改写后的update语句变为：

update t_balance set balance = balance + 50

通过增量更新，即使在并发场景下，也能保证每次update使用的旧值都为数据库中的最新值。