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:

Fetch the last balance for a given user
Create a new row in balances with a balance that is last balance + amount (amount can be negative)
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....

score 1 · Accepted Answer · edited Jan 25 '22 at 07:23

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

edited Jan 25 '22 at 07:23

snakecharmerb

47,570
11
100
153

answered Jan 25 '22 at 06:44

Laurenz Albe

209,280
17
206
263

But this is if balances are updated, my original thought was to have each balance change be a new row (in which case it isn’t clear what to lock), this way we can have a balance back in time (although I guess we could just reconstruct it through transactions). – Ilya Jan 25 '22 at 07:01
Without debating your data model, if you always lock the most current balance, that would be enough to prevent concurrent modifications. – Laurenz Albe Jan 25 '22 at 07:06
But the most current balance is not updated, only selected? – Ilya Jan 25 '22 at 07:40
Right. If you use `SELECT ... FOR NO KEY UPDATE`, that select will lock. – Laurenz Albe Jan 25 '22 at 07:56
@LaurenzAlbe I've asked a similar question where the balance is not stored. Do you have any suggestions? https://stackoverflow.com/questions/73120256/read-write-problem-on-an-append-only-table – Xiaoguo Ge Jul 26 '22 at 09:25

Preventing double spend in insert-only postgres db/sqlalchemy

1 Answers1