What I’m trying to do:
Understand data table views
What I’ve tried and what’s not working:
Read the documents to understand.
Code Sample:
#Server Side
@anvil.server.callable
def get_table_view():
return app_tables.table.client_readable(column_1=5)
#client Side
table = anvil.server.call('get_table_view')
data = table.search()
Are these searches done in a transaction? If it was client_writeable, would that write be done in a transaction?
My understanding is that you get Transactions only when you ask for them, explicitly; which can be done in Server or Uplink code, but not in Client code.
Got it. Given transaction can only be asked for on the server side, using table views forfeits the ability to search/get within a transaction?
Nope. You can use exactly the same views Server-side, too! In a Transaction.
First of all, thank you for your responses.
Could you expand on this? I am thinking in the context of the scenario above where a view is passed to the client and then searched.
Would you pass the view back to the server and perform that search in a transaction?
More likely I’d cache the view in server-side Session Storage, or just create it anew. Server-side code isn’t accessible to the browser, so it can’t be hacked. Anything it computes should be valid, including the view.
The same can’t be said of code and data sent to the Client. So passing a view back is probably not a good idea.
Very interesting…
Time to do some playground work.
Thank you!
The rule of thumb is to keep transactions as short as possible, and in order to keep them short, get them to run on the server.
One day I had a burst of calls to an uplink function with the in_transaction decorator.
The first call was keeping the transaction open for too long, about 4 seconds, because it was running remotely (a roundtrip every time the uplink runs a query) and because it was starting a transaction before it really needed to (the decorator wraps the whole function in the transaction, including steps that don’t need to, like checking for user permission, etc.)
The following calls would fail because the first transaction was open for 4 seconds, there were about 50 requests in about 30 seconds, and the database server crashed.
The Anvil staff rebooted the server and contacted me. I immediately removed the in_transaction decorator from the uplink function, got the uplink to call a server function that does the same job but much faster (because it’s closer to the database), so the transaction stays open for a shorter time, and managed the beginning and end of the transaction and the retry logic by myself.
Then I retested with 200 requests in 10 seconds and everything went smooth.
So my advice is to get the read only view without creating any transaction at all. Then use a transaction only when it’s time to write something back to the database. You can do that by sending the old row as it was received by the form plus the new updated info to a server function, start the transaction on the server function, verify that the involved rows have not changed, if not do the update, if yes simulate a transaction failure.
In other words, I wouldn’t use a writable view in a transaction.
I think this might be plaguing my application, as well. I didn’t know it could weigh down the server so much. As my user base has grown, I have had to go back and optimize the application. Views were my most recent refactor to better manage syncing, reduce round trips, and reduce background task.
I’ve also refactored a bit to remove some in_transaction decorators, but this could be a larger issue than initially perceived.
Thank you again for the insight!
This definitely takes some work. I never have an @anvil.server.callable function also be an @anvil.tables.in_transaction function. The server function does as much up-front work as possible, and then calls a transaction function to do the database updates. The idea is to get the transaction function executed as quickly as possible.
Avoid fetching rows in a transaction function that aren’t going to be updated in that function. That just widens the scope for false positive transaction conflicts.
I don’t know the details about the type of transactions Anvil does, but I have the feeling that (sometimes?) other rows / the whole table / the whole database are locked by the transaction.
I remember doing some tests and getting transaction conflicts when I wasn’t expecting them, but I don’t remember the scope.
Both writable views and in_transaction decorators are great for quick apps.
Writable views are great for simple cases that don’t require transactions, but I wouldn’t use them with transactions. I actually think the transaction lifecycle can’t last as long as a writable view, for the simple reason that a writable view could be left on the browser for hours. I guess this is the answer to your original question.
The in_transaction decorator is great for low traffic cases. I’m going to make up some numbers: on the server if you have up to 10 concurrent transactions and on the uplink if you have up to 2 concurrent transactions. The duration of the transaction is obviously a factor, but these are made up numbers, so who cares!
Did digging into the source, in_transaction wrapper eventually resolved to a clojure function called open-app-transaction!
Inside it, it uses JDBC (the java/JVM alternative to ODBC) to call the basic PostgreSQL isolation levels.
This means that:
row are treated at Read Committed Isolation Level
@anvil.tables.in_transaction(relaxed=True) interactions are set to Repeatable Read Isolation Level
@anvil.tables.in_transaction is set to the PostgreSQL version of Serializable Isolation Level
The explanation of transaction isolation itself along with who what and how locks are handled (by row / rows or by table, how it handles deadlocks etc) can be found at the top of the docs found on postgresql.org
EXTREMELY helpful in my understanding of things @stefano.menci @ianb @jshaffstall @p.colbert
Thank you!
I still need to read the documentation about 3-1000 times before grasping entirely, but I’ll get there!
