Database Schema Design: Tracking User Balance with concurrency

Question

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.

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

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.

Answer 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.

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.

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