Django Aurora RDS transaction read inconsistency

Question

I am using django atomic transactions to create objects. I am just writing new balances for a user (1 user, many balances) repeatedly.

For example, if a user starts with a balance of 10 and we want to add 2 new balances:

user.add_balance(5) -> user.balance = 15
user.add_balance(3) -> user.balance = 18

However, within the same atomic block, a subsequent balance write depends on reading the current balance, which was just affected by the previous write in the same transaction (I hope that makes sense). In other words, the second transaction above (adding 3) needs to read the balance as 15, NOT 10 which is the old balance.

Here is a simplified version of the code.

def add_user_balance(self, amount):
        WalletBalance = apps.get_model("wallet", "WalletBalance")
        # this is terse but it is just summing all of the user's balances to get the current balance
        current_balance = float(
            WalletBalance.objects.filter(user=self, cancelled=False).aggregate(
                Sum("amount")
            )["amount__sum"]
        ) # this does NOT use a cached relation from within the user model, it should go to the db and does in testing
        # HERE is where I need the new balance within the same transaction.
        if float(current_balance) + float(amount) < 0:
            raise ValueError("balance_low")
        balance = WalletBalance.objects.create(
            amount=amount, transaction_type=transaction_type,
        ) # this adds a new balance, and should reflect on the next write within the same transaction
        
        return balance

In testing, this works exactly as expected. However, this does not work in my production environment. In the same example above the first call to add_transaction (add 5) reads the current balance as 10 (which is correct), however, the second call (add 3) also reads the balance as 10 which is NOT correct (should be 15, reflecting the first write).

My development environment uses a standard postgres 13 server, which again is working correctly.
In production, I am using Amazon Aurora with postgres compatibility with separate reader and writer servers. I expect that this is the problem, because when I remove the atomicity everything works as expected (the same as in development).

This implies that the development postgres server reads correctly within the same transaction but the Aurora reader instance can't read writes within the same transaction produced by the Aurora writer.

My questions then are:

Is this expected behavior from Django ORM atomic transactions?
Is this expected behavior from Aurora with separate read and write servers? Again, this works in dev with a single postgres server handling reads and writes. Ideally would this not be the expected / desired behavior in Aurora?
Is my understanding of Django transactions with separate read and write servers correct and is this behavior documented anywhere? Please let me know where my understanding is wrong.

Edit:

Here is my settings.py for production showing the different reader and writer instances:

DATABASES = {
    "reader": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "dashdb",
        "USER": "postgres",
        "PASSWORD": env("RDS_PASSWORD"),
        "HOST": "rds...com",
        "PORT": 5432,
    },
    "default": {
        "ENGINE": "django.db.backends.postgresql_psycopg2",
        "NAME": "dashdb",
        "USER": "postgres",
        "PASSWORD": env("RDS_PASSWORD"),
        "HOST": "rds...com",
        "PORT": 5432,
    },
}

Django Aurora RDS transaction read inconsistency

0 Answers