My understanding was that a transaction conflict should show up only if two server calls modify the same piece of data…same field and same row of a data table. Or possibly two different fields in the same row? Either way, it’d need to be the same row being modified to cause a conflict.
I have a timer setup to call a server function periodically. In that server function it looks up the current user’s entry based on a unique token and sets a datetime field to now. Lots of calls to the server function from different browser tabs set this field in different rows, and that seems to work okay for a while.
Eventually, though, I get a transaction conflict on this line of code:
me[‘last_ping’] = datetime.datetime.now()
That object is one I look up based on a unique token for each user. I know that’s unique, because the other information passed back in that object matches the user.
Assuming that I don’t have some error (which I’m checking on) that’s causing two server calls to actually refer to the same user, what other things should I look for?
I reference that field a fair amount (to determine is the user’s app is still active), but only set it in that one spot.
I’m familiar with @tables.in_transaction. For what it’s worth, when you use @tables.in_transaction, any exception (including syntax errors or non-DB related exceptions) will cause the transaction to be retried. Definitely not a good thing to use while in development mode.
My original question was why was this a transaction conflict, when the rows being updated would be different for each user. If I can understand that, I can work out the best approach to fixing the problem. I designed the data tables with concurrency in mind, so that each user would only be updating their own information, and not anyone else’s information, so I was surprised to see the conflict.
Does anyone know specifics of which of these situations would cause a transaction conflict in Anvil?
Two users read the same row at the same time
One user reads a row and another user writes the row
Two users modify different rows in the same data table
#2 is what it seems like I’m seeing. That’s not what I expected from transactions, but if that’s how Anvil works then I’ll need to work around it.
Here’s a clone link to a minimum app that shows the conflict (eventually)
I added 6 players and then opened up each individual player’s link in a different browser tab. Eventually the transaction conflict kicked off.
There’s a section in the server function that you can comment out to avoid the conflict (I ran with that commented out for about an hour without seeing the conflict). The only thing being done in that section is reading of player rows. So it seems the conflict is kicked out when one user is reading a row from another user and that other user is trying to update their own row.
Anvil’s data tables are actually views in the underlying postgresql db and there is a series of database triggers that handle the synchronisation between those views and the actual tables. (This is what enables you to create data tables and edit their definition dynamically).
Because of this, it’s entirely possible (likely even), that postrgresql will be using a lock strategy for transactions that’s broader than just a single row.
For what it’s worth, when you use @tables.in_transaction, any exception (including syntax errors or non-DB related exceptions) will cause the transaction to be retried.
Actually, @tables.in_transaction is more clever than that! It only retries when you get a TransactionConflict exception – other exceptions pass straight out.
(If you can get it to retry after any non-transactional error, that’s a bug, and should be reported!)
It’s been about a year, so things might have changed, but I was getting an exception from Stripe and that triggered a retry by @tables.in_transaction. If I can get this current issue sorted, I’ll go back to play with that one.
Any further guidance from the Anvil folks about which circumstances should be causing transaction conflicts and which shouldn’t? I’ve been going under the assumption that individual rows are the locking level and only concurrent writes are an issue.
A bit more info. I tried putting the lines of code that were getting the transaction conflict into a try/except block, thinking that if I just skipped processing that time through, the next timer tick would try again.
The clone link above is to the most recent version of the test code.
I’m seeing the message about transactions aborted showing up on the page, which is what I’d expect. In a normal app I wouldn’t show those, but for testing I wanted to know how often they were happening.
But then, I’m getting a few actual errors on the page in sequence:
AnvilWrappedError: This transaction has expired
AnvilWrappedError: Internal server error: c2450143b6f7
RuntimeUnavailableError: Server resources not available for 'python3-sandbox'.
The hex code for the internal server error is different each time it comes up.
Here’s the code snippet I changed, in case you don’t want to look at the clone:
try:
with tables.Transaction():
me = app_tables.players.get(token=token)
if not me:
return "Player not found for token"
me['last_active'] = datetime.datetime.now()
except tables.TransactionConflict:
return "Aborted due to transaction conflict"
As a reminder, it’s that setting of the last_active field for the current player that was causing the original transaction conflict. Any string return from this server function is just logged on a text area on the page so I know what happened.
Am I doing something in this code sequence that would cause these errors to show up? If you want to play with the clone, it doesn’t take long for the errors to appear.
Forgot to mention, in the clone link above the domain name is hard coded into the Home->Row Template 1->link_1.text data binding. That’ll need changed if you clone it and want to run it.
Can you tell us a little more about how transactions are handled in Anvil? There’s some question about whether it’s locking one row or the entire table, and about whether you can get a conflict with one writer but many readers.
We perform transactions with postgres, using its SERIALIZABLE isolation level. We sort of deliver ourselves into Postgres’s hands here - all we promise is that if the transaction commits, it will be serialisable (ie no data you read during the transaction was changed before the results became visible to everybody).
