数据库内核月报 － 2022 / 12

业务背景

账务系统的业务中从用户账号扣款，如果发现用户账户余额充足情况下，100次扣款数据库都返回成功，但其中有20次没有真实扣款，那业务方一定会震惊。但如果业务设计不合理，数据库中是会存在这种现象的。

数据库场景

在MySQL中看下面一个场景。 业务中存在一张用户账户表，存有用户的账户金额。业务表做了如下设计。业务中使用RC隔离级别。

CREATE TABLE `user_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `balance` decimal(16,3) NOT NULL DEFAULT '0.000' COMMENT '用户余额',
  PRIMARY KEY (`id`),
  KEY `idx_userid_balance` (`user_id`,`balance`)
) ENGINE=InnoDB;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

账户存在如下数据

INSERT INTO user_account VALUES(1, 500, 1000);
INSERT INTO user_account VALUES(2, 600, 2000);

mysql> select * from user_account;
+----+---------+----------+
| id | user_id | balance  |
+----+---------+----------+
|  1 |     500 | 1000.000 |
|  2 |     600 | 2000.000 |
+----+---------+----------+

当业务并发扣款，出现如下场景时候（transaction_isolation为READ-COMMITTED）（在测试的时候可以将innodb_lock_wait_timeout调大防止Lock wait timeout）：

3个线程，并发扣款3次。这个时候我们查看结果，只有两次真正的做了扣款。

mysql> SELECT * FROM user_account;
+----+---------+----------+
| id | user_id | balance  |
+----+---------+----------+
|  1 |     500 |  998.000 |
|  2 |     600 | 2000.000 |
+----+---------+----------+

我们在看3个session的UPDATE操作返回结果也可以看到，其中session3没有更新数据行。 Sesssion1和Session2返回的是：

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

而Session3 update返回的是：

Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

这里session1和session2更新后，user_id的balance明明是998，session3 Update的where条件是balance > 1，但却没有匹配到数据行，也没有更新。Update返回成功，但钱未做扣款。

原因分析

我们看一下UPDATE语句的执行计划，Update语句选择的是二级索引idx_userid_balance。

mysql> explain UPDATE user_account SET balance = balance -1 WHERE user_id = 500 and balance > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: user_account
   partitions: NULL
         type: range
possible_keys: idx_userid_balance
          key: idx_userid_balance
      key_len: 12
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using temporary

接下来对上面3个session并发操作时数据在引擎InnoDB中的变化及每个session的处理做逐步分析。 在3个session操作前，两条数据在二级索引叶子节点上如下分布。

session1对user_id=500做Update操作，将rec1标记删除，然后新插入rec3，语句执行后如下：

session2对user_id=500做Update操作，定位到rec3，由于session1持有该行上的锁还未释放所以会等待。 session3对user_id=500做Update操作，也定位到rec3，这个时候也会排队等锁。

当session1提交后，session2被唤醒restore cursor继续定位到rec3上。然后将rec3标记删除，插入rec4。

这个时候session3继续在等锁，当session2提交后，session3被唤醒，restore cursor继续定位到rec3上。这个时候rec3已经被标记为删除，session3逐行读取next record，找到rec2后发现已经超过查找的上边界(500, max)，然后停止查找。session3未找到匹配的数据，然后返回成功，未更新任何记录。 其实上述问题是由RC隔离级别下的幻读导致。可以查看社区文档，事务隔离级别和Phantom Rows。

Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps. For information about phantom rows, see Section 15.7.4, “Phantom Rows”.

修复建议

一、修改索引，让业务快速修复止血

其实用户表中每个user_id只会有一行数据，索引idx_userid(user_id)就可以达到非常好的过滤性。这个时候可以选择创建idx_userid，删除原来的索引idx_userid_balance。 这个时候更新语句通过二级索引定位更新后，二级索引上的数据并不会发生变化，从而避免了上面的问题。

二、改隔离级别为RR隔离级别

当我们的更新操作都改为RR隔离级别，上面的操作会有什么结果呢。我们在每个session执行前执行：

set transaction_isolation='repeatable-read';

可以看到 session1和session2中update语句返回：

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

而session3返回如下：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这是因为RR隔离级别下，会对数据可能插入的前后范围加锁，InnoDB行锁可以查看如下文章。 在这个场景中session2和session3在发起update的时候都会等rec3上的LOCK_ORDINARY锁，session2获得锁后，继续插入其插入意向锁又会被session3阻塞，导致死锁发生。

扩展

RC隔离级别， set transaction_isolation=’read-committed’。如果session中同时有select for update，那每个语句执行的结果会是什么样子，可以试一试。

这里由于FOR UPDATE语句会阻止其他并发事务SELECT FOR UPDATE语句的执行，所以每个事务的UPDATE语句执行期间都没有其他UPDATE语句在执行，最终每个事务都能扣款。但是这里session2和session3的SELECT FOR UPDATE语句返回的会是empty set，没有读到对应的记录，因为RC隔离级别下FOR UPDATE也不会加范围锁。InnoDB各个场景下数据行的加锁情况，可以参考文章InnoDB行锁分析。

如果原始场景update语句是加余额，set balance = balance + 1，几个session的操作结果又是什么样子。

由于加余额后，新插入数据是在session2/session3事务等待数据行的扫描序后面，所以session2/session3可以读到对应数据做更新。三个事务都会完成加余额操作。