mysql - 有效处理账户往来余额

Question

我一直在开发一个应用程序来处理账户和通过这些账户进行的交易。

目前，应用程序使用的表格按照以下方式建模:

account+----+-----------------+---------+-----+| id | current_balance | version | ... |+----+-----------------+---------+-----+| 1  | 1000            | 48902   | ... || 2  | 2000            | 34933   | ... || 3  | 100             | 103     | ... |+----+-----------------+---------+-----+account_transaction+------+-------------+----------------------+---------+------------------+-----+|  id  | account_id  |          date        |  value  | resulting_amount | ... |+------+-------------+----------------------+---------+------------------+-----+| 101  | 1           | 03/may/2012 10:13:33 | 1000    | 2000             | ... || 102  | 2           | 03/may/2012 10:13:33 | 500     | 1500             | ... || 103  | 1           | 03/may/2012 10:13:34 | -500    | 1500             | ... || 104  | 2           | 03/may/2012 10:13:35 | -50     | 1450             | ... || 105  | 2           | 03/may/2012 10:13:35 | 550     | 2000             | ... || 106  | 1           | 03/may/2012 10:13:35 | -500    | 1000             | ... |+------+-------------+----------------------+---------+------------------+-----+

每当应用程序处理一个新交易时，它都会在 account_transaction 中插入一个新行，并在 account 表中更新列 current_balance 存储帐户的当前余额和用于乐观锁定的列 version。如果乐观锁定有效，则提交事务，如果无效，则回滚事务。

举个粗略的例子，在处理事务102时，应用程序做了以下是伪 SQL/JAVA:

set autocommit = 0;insert into account_transaction(account_id, date, value, resulting_amount)values(2, sysdate(), 550, 2000);update account set     current_balance = 2000,     version = 34933  where     id = 2 and    version = 34932;if (ROW_COUNT() != 1) {    rollback;}else {    commit;}

然而，某些帐户非常活跃并接收许多并发事务，这会导致 MySQL 在更新 account 表中的行时出现死锁。这些死锁对应用程序造成了严重的性能损失，因为它会导致在数据库发生死锁时重新处理事务。

如何有效处理账户的当前余额？需要当前余额来授权/拒绝新交易，并用于各种报告。

Answer 1

How can I efficiently handle the current balance for the accounts?

我认为整个模型设计过度。

放弃通过 version 的乐观锁定并有一个简单的......

UPDATE account SET current_balance = current_balance + value WHERE id = ...

...在插入新account_transaction 的事务的结尾 应该很快。为了数据完整性，考虑将其放入 account_transaction¹ 上的 AFTER INSERT 触发器。

• 首先，你是在事务结束时做的，所以即使事务很长，这一行上的锁争用也应该很短。
• SQL 保证在单个语句中查看一致的数据，因此无需单独的 SELECT ... FOR UPDATE。
• 此外，由于您是添加一个值，而不是或直接设置总和，因此这些操作的执行顺序并不重要 - 加法是可交换的(因此较短的交易可以“超过"较长的)。

---

¹ 但注意不要过早触发它——只有在完全“煮熟”时才插入新的 account_transaction，不要(例如)提前插入但稍后更新 resulting_amount。