General Ledger Concurrency (django atomic operations)

Question

I am wondering how to deal with concurrency given a general ledger. Consider schema like this:

id   | account_id | credit | debit | balance |
1    | 123        | 0      | 100   | 200     |
2    | 456        | 100    | 0     | 100     |

To add a new entry to the ledger, I would do (pseudo code):

last_entry = get last account entry
is_credit = figure out if it is debit or credit entry
is_liability = figure out type of account

new_entry = Entry(
    foo='bar'
    # etc
)

if is_liability and is_credit
    new_entry.balance = last_entry.balance + amount

if is_liability and !is_credit
    new_entry.balance = last_entry.balance - amount

if !is_liability and is_credit
    new_entry.balance = last_entry.balance - amount

if !is_liability and !is_credit
    new_entry.balance = last_entry.balance + amount

new_entry.save()

The problem I see with such approach is:

Let's say a request comes and I have to enter new entry in the ledger. The new entry is going to increase the account balance.

What if in the middle of running the above code (let's say after getting the last entry) there is another request which will increase the balance again.

So the balance will be increased once, the other request will save a new entry with the same balance as it will just use something like:

new_balance = last_entry.balance + amount

But the last_entry has been outdated by another request so the balance is higher now.

Any ideas how to make sure a situation like this does not happen (I know this would not be very likely).

UPDATE:

Following some answers, I have come up with this solution using SELECT FOR UPDATE:

    with transaction.atomic():
        new_entries = prepare_entries()
        for new_entry in new_entries:
            new_entry.save()

Is this a good way to get around potential concurrency issues?

ndpu · Accepted Answer · 2014-01-24T13:16:44.230

2

You can use select_for_update (returns a queryset that will lock rows until the end of the transaction):

with transaction.atomic(): # or commit_on_success/commit_manually in django < 1.6
    new_entries = prepare_entries()
    new_entries.select_for_update() # lock to update only in current transaction
    for new_entry in new_entries:
        #change new_entry somehow
        new_entry.save()

or F expression:

An F() object represents the value of a model field. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

For example:

last_entry.update(balance=F('balance')+amount)

edited Jan 24 '14 at 13:16

answered Jan 23 '14 at 18:54

ndpu

22,225
6
54
69

Could take a look at my code? I have added some example code to my question. Do you see any problem with it? – Richard Knop Jan 23 '14 at 19:26
Thanks. I see now. So changes made by one open transaction are visible to other running transactions. I updated my code sample. It's much simpler now. – Richard Knop Jan 23 '14 at 19:49
Note that for `select_for_update` to work, you need to be inside a transaction. – Maciej Gol Jan 24 '14 at 09:34
@kroolik since django 1.6 it is default behavior, you are always inside a transaction, check docs about autocommit here https://docs.djangoproject.com/en/dev/topics/db/transactions/#django-s-default-transaction-behavior – ndpu Jan 24 '14 at 09:49
You don't want autocommit for `select_for_update` as committing releases the lock. – Maciej Gol Jan 24 '14 at 09:55
@kroolik hm, you are right, it turns out that `select_for_update` since 1.6 is useless (if autocommit is on)? because code must be wrapped in `atomic` anyway... http://stackoverflow.com/questions/17149587/select-for-update-in-development-django, https://docs.djangoproject.com/en/dev/topics/db/transactions/#select-for-update – ndpu Jan 24 '14 at 10:16
`select_for_update` has always been requiring a transaction to work. And autocommit being on or off has nothing to do with it, as transaction overrides the autocommit behaviour. The only thing that had changed since 1.6 is using `transaction.atomic()` instead of `transaction.commit_on_success()` or `transaction.commit_manually()`. And it's not useless, as it's the only way to lock a row for extended period of time. – Maciej Gol Jan 24 '14 at 12:39

score 1 · Answer 2 · answered Jan 23 '14 at 18:52

Assuming your database supports it (and for this, it should), wrap the entire operation in a transaction. I.e start with a 'start transaction' call, and end with a commit.

This guarantees that the entire transaction is performed, or none of it is. You may also need to lock the table while you do this to ensure a consistent appearance for other processes.

Exactly what you do, and how you do it is usually database dependent since the relationship between transaction handling and row- and table-locking varies from database to database and engine to engine.

score 1 · Answer 3 · answered Jan 23 '14 at 18:53

1

Calculate total difference that would applied to the balance and use the update query:

Model.objects.filter(pk=entry.pk).update(balance=F('balance') + difference)

answered Jan 23 '14 at 18:53

Maciej Gol

15,394
4
33
51

In this call, can I also atomically check if the balance stays positive? or should I check it afterwards and rollback if it became negative? – EralpB Sep 12 '17 at 14:47

General Ledger Concurrency (django atomic operations)

3 Answers3