Preventing double spend in insert-only postgres db/sqlalchemy

Question

I'd like to track financial transactions (in an in-app currency) in a set of insert-only tables (with postgres backend). A minimal example of how we define it is here:

class Balance(db.Model, CreatedTimestampMixin):
    """Balances table"""

    __tablename__ = "balances"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True)
    balance = db.Column(db.Integer, default=0)

    # one-to-one each balance change has a transaction
    transaction = db.relationship(
        'Transaction', uselist=False, backref=db.backref('balance'))

    def __repr__(self):
        return f"<Balance {self.id}>"


class Transaction(db.Model, CreatedTimestampMixin):
    """ Transactions that cause balance changes """

    __tablename__ = "transactions"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    balance_id = db.Column(db.Integer, db.ForeignKey(
        "balances.id"), nullable=False)
    amount = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f"<Transaction {self.id}>"

In this picture when a transaction occurs, the following steps are taken:

1. Fetch the last balance for a given user
2. Create a new row in balances with a balance that is last balance + amount (amount can be negative)
3. Create a new row in transactions with a foreign key to the new balance and the amount.

However, if two different transaction requests come at the same time, this could create a double spend (the old balance is fetched, and then two transactions are entered). I would also ensure that old balance + amount >=0 but if I allow double spend then I could allow dipping below 0.

How do I avoid double spend/ensure that transactions are processed one after another? I kind of understand that solutions could be either by creating some kind of lock or by enforcing parent/child ordering, but not sure what is the correct way to implement it....

Answer 1

The canonical way to do that is to serialize reads of the balance with

SELECT ... FOR NO KEY UPDATE

That places a lock on the row that is held until the database transaction completes and blocks other such statements.

Note that you will have to keep database transactions short if you want that method to be efficient. An alternative method would be to use “optimistic locking”.