Transaction collisions with multiple background functions

Anvil Q&A
1

What I’m trying to do:
I have a background task that runs periodically, while I also have a function that runs whenever an email comes in (both are running in the background). I have tried everything to prevent transaction collisions, but nothing seems to work? I am using @tables.in_transaction on EVERYTHING that makes a call to or from a table. What am I missing here?

What I’ve tried and what’s not working:

Code Sample:

# this is the error code
anvil.tables.TransactionConflict: Another transaction has changed this data; aborting

Clone link:
share a copy of your app

2

You’d need to actually show the code for anyone to have any chance of providing specific advice.

In general, though, keep your transaction functions small and call them from your background functions. Try to not search inside a transaction function, keep those for updates. If all else fails, rework things so both background functions aren’t modifying the same tables.

1 Like
3

I can’t share all of my code, but I will show an example. Also, I can ABSOLUTELY not control when a transaction hits the same table. If this is something that ANVIL cannot properly do, I will have to completely abandon ANVIL. I thought the whole purpose of using @tables.in_transaction was to prevent these kinds of collisions?

EXAMPLE CODE:

@anvil.server.background_task
def process_new_spa_guide_lead(msg):
  
  """ IN TRANSACTION FUNCS """
  @tables.in_transaction
  def __add_record(message, owner):
    search = app_tables.parsedemails.search(tables.order_by("parsed_id", False))
    last_id = search[0]['parsed_id'] if len(search) > 0 else 0
    next_id = last_id + 1
    details = message.get"parsed")
    kwargs = {
      "parsed_id": next_id,
      "date": datetime.now(anvil.tz.tzutc()),
      "full_name": details.get("name"),
      "email": details.get("email"),
      "phone": details.get( "phone"),
      "address": details.get("address"),
      "timeline": details.get("timeline"),
      "source": details.get("source"),
      "message_date": message.get("date"),
      "message_text": message.get"text"),
      "message_html": message.get"html"),
      "message_headers": message.get"headers"),
      "client": owner,
    }
    record = app_tables.parsedemails.add_row(**kwargs)
    
    return record

  message = {} # some message dict
  owner = obj # a row from another table
  record = __add_record(message, owner)
4

The purpose is to prevent outdated data from being updated, thus leaving your data tables in an inconsistent state. @tables.in_transaction also adds in a retry mechanic, under the thinking that if the problem was timing, trying again after a short delay might help.

Nothing is going to prevent transaction conflicts in a multi-user system, but an awful lot of people on the forum have managed to make Anvil work in such situations.

Let’s go back to the general advice I gave:

call them from your background functions

You aren’t decorating the background function itself with the transaction, which is perfect

Try to not search inside a transaction function

You are searching inside your transaction function, and you’re getting all the rows. This is pretty much guaranteed to lead to a transaction conflict, if another function modifies the data table (e.g. adding a row), since it invalidates the search results the transaction is depending on.

If looks like you’re doing that to get the next sequence id. There are more compact ways of doing that that don’t require you to do a search on the entire table, and that play better with transactions. Look at [ANSWERED] Auto Incrementing Field - #10 That can be extended to naming your sequences so that you can have multiple sequences in play.

keep your transaction functions small

You’re doing work inside the transaction decorator that could be done outside of it, before you call __add_record. Keep the code inside the transaction function to just the minimum needed to update the data table, that’ll minimize the chances of conflicts.

In your case, get the current datetime and build your dictionary outside of the transaction. Get your next sequence number outside of __add_record as well.

If you switch to sequence numbers, it’s debatable whether your __add_record even needs to be a transaction, since at that point it’s just adding a row.

4 Likes
5

The decorated function is very short and should run very quickly. Knowing how long the function takes would help. Can you print some timestamps before and after the function is called, so we know what we are talking about?

I see two reasons why you could end up with transaction conflicts here:

  1. You are executing this hundreds of times per second.
    If this is the case, there is nothing much you can do. You may need to use a faster database.

  2. You are not executing this on the server, that is you are executing this either on an uplink script or on the client.
    If this is the case, then the uplink / client should call a server function so that code is executed in the server and the transaction starts and ends in the server.

Since you are only using one row, perhaps using get instead of search will slightly speed up the job and decrease the chances of transaction conflict.

6

It appears having the search functions in the @tables.in_transaction was causing a lot of the issues. I put only the update and add_row functions in the decorator and that seemed to help.

Thank you, guys, for your help!

1 Like