Database Row uses stale values while looping over rows

Anvil Q&A
1

What I’m trying to do:
I’m looping over all rows in the table (the table has about 150 rows) but each iteration in the loop can take let’s say 30 seconds. If one value in a row is updated after the loop has started then I get the old value not the new value when I access it the first time in the iteration. However, when I access it again in the iteration later (after referencing a bunch of other items in the same row and updating some), I get the right (updated) value.

What I’ve tried and what’s not working:
Tried to access it twice in the beginning but it didn’t help. I am trying to know what is the right way to access it while making sure I get the most recent value.

Code Sample:

  loans = app_tables.loans.search()
  # each iteration of the following loop takes a while so some rows may be updated in between (in a different thread)
  for loan in loans:
    end_time = loan['end_time'] # This is the wrong old value
    if datetime.now(anvil.tz.tzutc()) > loan['end_time']:
        # do a bunch of things here that also involves DB reading and writing
        end_time = loan['end_time'] # This is the right updated value

Clone link:
share a copy of your app

2

Most of the times this is the desired approach.

Imagine a function is counting how many candies you have on your shelves while another function is moving some candies from one shelf to another: the first function could count 100 candies on the first shelf, then the second function moves 50 candies from the first shelf to the second, then the first function counts those candies again when it works on the second shelf.

Or imagine a datagrid showing 10 items on the first page, then item 5 is sold by another function, then you go to the second page and you will never see item 11 because now it’s item 10 and doesn’t belong in the second page.

The rule of the thumb is that when a function starts, it works with a snapshot of the database for the duration of its job. Often this is so important that you use transactions to make sure that not only one search shows things the way they were when the search started, but you want all the following searches in the same function to see the database the way it was when the function started, so candies are counted consistently on whatever shelf they were when the function started and pages show items the way they where when the first search started.

And you don’t see an invoice that sold something on one table while that something is still on the other table. You would ensure this by creating one transaction in the function that adds the invoice row to one table and removes the items from the other table, and one transaction that searches the two tables at different times (a few hundred of seconds apart) so it shows consistent values.

So this explains (I hope) why you see that behavior. Look for transactions if you want more details.

Perhaps the question is now “how do I make sure my function doesn’t work with stale data”.

If that is the case, then we need more details, because it is not a standard case.

2 Likes
3

You can manually update a row by calling loan.update() without any arguments.

2 Likes
4

Thanks for the detailed explanation. That’s helpful; I’ll take a closer look at how transactions work.

5

Thank you, I’ll try that!

1 Like