数据库模式设计:跟踪并发的用户平衡

In an app that I am developing, we have users who will make deposits into the app and that becomes their balance.

在我正在开发的一个应用程序中，我们的用户会将存款存入这个应用程序，这就是他们的余额。

They can use the balance to perform certain actions and also withdraw the balance. What is the schema design that ensures that the user can never withdraw / or perform more than he has, even in concurrency.

他们可以使用余额来执行某些操作，也可以提取余额。什么是模式设计，它确保用户永远不能退出/或执行比他拥有的更多，即使是在并发性中。

For example:

例如:

CREATE TABLE user_transaction (
  transaction_id SERIAL NOT NULL PRIMARY KEY,
  change_value   BIGINT NOT NULL,
  user_id        INT NOT NULL REFERENCES user
)

The above schema can keep track of balance, (select sum() from user_transction); However, this does not hold in concurrency. Because the user can post 2 request simultaneously, and two records could be inserted in 2 simultaneous database connections.

上面的模式可以跟踪平衡(从user_transtion选择sum()));但是，这在并发性中并不成立。因为用户可以同时发布两个请求，两个记录可以插入到两个同步的数据库连接中。

I can't do in-app locking either (to ensure only one transcation gets written at a time), because I run multiple web servers.

我也不能进行应用内锁定(以确保每次只编写一个收发)，因为我运行多个web服务器。

Is there a database schema design that can ensure correctness?

是否有数据库模式设计可以确保正确性?

P.S. Off the top of my head, I can imagine leveraging the uniqueness constraint in SQL. By having later transaction reference earlier transactions, and since each earlier transaction can only be referenced once, that ensures correctness at the database level.

我可以想象利用SQL中的惟一性约束。通过拥有较早的事务引用，并且由于每个较早的事务只能被引用一次，这就确保了数据库级的正确性。

2 个解决方案

#1

Relying on calculating an account balance every time you go to insert a new transaction is not a very good design - for one thing, as time goes by it will take longer and longer, as more and more rows appear in the transaction table.

每次插入一个新事务时都要计算帐户余额，这并不是一个很好的设计——首先，随着时间的推移，随着事务表中出现越来越多的行，这将花费越来越长的时间。

A better idea is to store the current balance in another table - either a new table, or in the existing users table that you are already using as a foreign key reference.

更好的方法是将当前余额存储在另一个表中——要么是一个新表，要么是您已经将其用作外键引用的现有用户表。

It could look like this:

它可以是这样的:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    balance BIGINT NOT NULL DEFAULT 0 CHECK(balance>=0)
);

Then, whenever you add a transaction, you update the balance like this:

然后，每当您添加一个事务时，您就像这样更新余额:

UPDATE user SET balance=balance+$1 WHERE user_id=$2;

You must do this inside a transaction, in which you also insert the transaction record.

您必须在事务中执行此操作，在事务中您还需要插入事务记录。

Concurrency issues are taken care of automatically: if you attempt to update the same record twice from two different transactions, then the second one will be blocked until the first one commits or rolls back. The default transaction isolation level of 'Read Committed' ensures this - see the manual section on concurrency.

并发问题会自动处理:如果您试图从两个不同的事务中两次更新相同的记录，那么第二个将被阻塞，直到第一个提交或回滚。“Read Committed”的默认事务隔离级别确保了这一点——请参阅有关并发的手动部分。

You can issue the whole sequence from your application, or if you prefer you can add a trigger to the user_transaction table such that whenever a record is inserted into the user_transaction table, the balance is updated automatically.

您可以从应用程序发出整个序列，或者如果您愿意，可以向user_transaction表添加一个触发器，以便每当将一条记录插入user_transaction表时，余额将自动更新。

That way, the CHECK clause ensures that no transactions can be entered into the database that would cause the balance to go below 0.

这样，CHECK子句确保不会将任何事务输入到数据库中，从而导致余额低于0。

#2

Martin Fowler's notes on accounting patterns might be helpful to you, http://martinfowler.com/eaaDev/AccountingNarrative.html

Martin Fowler关于会计模式的笔记可能对您有所帮助，http://martinfowler.com/eaaDev/AccountingNarrative.html

#1