Account Update without explicit lock

bulk.py

from django.db.models import Case, Value, When, F, Q

def transfer(from_account: Account, to_account: Account, amount: int):

    with transaction.atomic():

        count = Account.objects.filter(
            Q(pk=from_account.pk, balance__gte=amount) | Q(pk=to_account.pk)
        ).update(
            balance=Case(
                When(pk=from_account.pk, then=F("balance") - amount),
                When(pk=to_account.pk, then=F("balance") + amount),
            )
        )
        if count != 2:
            raise InsufficientBalance()

simple.py

from django.db.models import Case, Value, When, F, Q

def transfer(from_account: Account, to_account: Account, amount: int):

    with transaction.atomic():

        count = Account.objects.filter(pk=from_account.pk, balance__gte=amount).update(
            balance=F("balance") - amount
        )

        if count != 1:
            raise InsufficientBalance()

        Account.objects.filter(pk=to_account.pk).update(balance=F("balance") + amount)

source.md

This django con talk https://youtu.be/iFM8DZaeM_M?si=YQjSq0x1KU2ZtIRE

The sql update statement lock the rows before applying changes, which is why the locking is not explicit, but there is still some locking involve.

The sql equivalent of the bulk version will look something this

BEGIN TRANSACTION;

UPDATE "accounts_account"
SET "balance" = CASE 
   WHEN ("accounts_account"."id" = %s) THEN ("accounts_account"."balance" - %s)
   WHEN ("accounts_account"."id" = %s) THEN ("accounts_account"."balance" + %s)
   ELSE NULL
 END
 WHERE (("accounts_account"."balance" >= %s AND "accounts_account"."id" = %s) OR "accounts_account"."id" = %s)

COMMIT;