Transactions not working as expected SQLAlchemy

Question

I am building a python flask backend which needs to be ACID. The usage is like a bank balance one must go up exactly but how much the other has come down.

I am using heroku with postgres as well as SQLAlchemy for my ORM. I initialise the db like this:

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy()
db.init_app(app)

The JSON body takes the phone number for the recipient of the payment as well as an access token for the payment sender. The whole route is large so I will not attach the data validation only the actual db operations. So:

# getting the sender
user = User.verify_auth_token(data.get('access_token'))
# getting recipient (phone is unique)
recipient = db.session.query(User).filter_by(phone=data.get('phone')).first()

if recipient is None:
    return make_error("Recipient does not exist")    
else:
    try:
        good1 = user.update_balance(amount, True, ticker)
        good2 = recipient.update_balance(amount, False, ticker)
        transaction = Transaction(sender_id=user.id, recipient_id=recipient.id, amount=amount, operation="regular")
            
        if good1 is None and good2 is None and transaction is not None:
            db.session.add(transaction)
            db.session.commit()
        else:
            db.session.rollback()
            return make_error(good1)
            return make_error(good2)
            
        response["success"] = True
        response["transaction"] = TransactionNested(many=False).dump(transaction)
        return response

    except:
        db.session.rollback()
        return make_error("Something went wrong")

To manipulate the balances I use the update_balance function which is part of user. That function is the following:

def update_balance(self, amount, sender, ticker):
    balance = self.balances.filter_by(curr=ticker).first()
    
    if balance is None:
        return "Balance not supported by user"
    
    if sender:
        if balance.amount < amount:
            return "Not enough funds to send"
        else:
            balance.amount = balance.amount - amount
    else:
        balance.amount = balance.amount + amount

So either the function returns a string error or it does not return anything on success. The issue the following: When I run say 3 different shell windows and run a bot which does the same transaction over and over again the sum of the balances of the two users is incorrect. This means that there is something here that is making the transactions non acid. For example if I send 10 from user 1 to user 2 1000 times in a loop with say time.sleep(0.5) and 3 different shells I will end up with discrepancies in balance of hundreds when there should not be a single unit of discrepancy.

Any help would be greatly appreciated.

Answer 1

It seems you're using default isolation level (Read committed for postgresql), but your code assumes that values aren't changing during execution. For example, you read balance 100, substitute 10 and going to write 90, but during that time the balance may be already 70 (it has changed by other workers), so you'll overwrite this value by incorrect one (90).

To change this behavior, you need to run the code serially, rather than concurrently: either on the database side (by changing isolation level to Serializable) or by the architecture (putting all requests in a queue).

Changing transaction isolation level to Serializable seems to be the most straightforward solution. But it also has downsides: you'll need to adapt your code to the logic of re-try transactions, when serialization fails:

SQLALCHEMY_ENGINE_OPTIONS = {'isolation_level': 'SERIALIZABLE'}

Running a task queue can be done via celery and it also has trade-offs (setup complexity, etc).

Answer 2

update_balance seems to be missing

db.session.add(balance)