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.