What I’m trying to do:
Find a way to simulate nested database transactions.
If we ask to start a transaction, but one is already started, then it raises an exception, when what we usually want to do is just “piggyback” on that existing transaction.
However, if we can query the database, and find that we’re already in a transaction, then we can avoid asking again. Otherwise, we have to wrap all transaction access in a global object (proxy?), which will keep track for us, and use the proxy instead.
maybe create a wrapper like the one below, but the exception checks for the error raised when there is already a transaction running, and just calls that function without the “With Transaction” line
def custom_transaction(fn):
@wraps(fn)
def wrapped(*args,**kwargs):
attempts = 0
while attempts < 3:
try:
with anvil.tables.Transaction() as txn:
return_val = fn(*args,**kwargs)
return return_val
attempts = 3
except anvil.tables.TransactionConflict:
attempts += 1
if attempts == 3:
raise anvil.tables.TransactionConflict
return wrapped
I was thinking, since at most 1 transaction can ever be under way at a time, we can track it with a single global counter.
Start with the global counter at zero. Then, when asked to start a transaction, we increment that count, and if it is 1, we really do start a transaction. We decrement the count whenever a transaction scope ends. When it drops to zero, we commit the transaction.
Of course, then we’d have to use this wrapper exclusively, and never invoke code that uses “raw” Anvil transactions. Feasible, if it’s all your own code. Less so if you’re calling third-party code.
but I think you’re talking to calling the same other_function in parallel?
if that’s the case, I think a global counter would work, but then you’d need a global counter for each type of transactions (different tables, different rows, different other stuff = different transactions)
Which could get messy and probably just be easier to retry on a conflict.
Yes, that’s the simplest example. In general, the distance to other_function might be a dozen function-call-levels removed from function.
Suppose, for example, you were designing a SQL-style Triggers system, for database tables. (This might be used, for example, to enforce data integrity, including relational constraints.) A trigger might indirectly invoke another trigger, an arbitrary number of levels deep. If anything goes wrong, all the interim changes, up to the outermost transaction, would have to be rolled back.
If the wrapper keeps track of the nesting level, then the trigger system doesn’t have to know or care which of its “transactions” was the outermost. It can be implemented more simply.
Is not 100% true all the time*, and two transactions can exist simultaneously even in the same table at the same time, as long as any changes that are made are completely time independently serializable.
(It snapshots the rows read/written to, and if it can, will make any and all updates that can happen if ALL of those changes do not conflict with each other by changing anything that they depended on to use in the transaction)
What you are describing is more like a table level lock, and PostGreSQL does not do this, but you can create any sort of blocking you want I guess. It’s just going to let less things proceed than the standard ‘serializable’ isolation level would. (The highest available in PGSQL) You might also have to deal with memory level race conditions with a global lock variable and the GIL.
*When we are using anvil the way we do it is quite often true that transactions fail, due to lack of a pure access to something like an UPDATE statement.
It isn’t really “nested”, since we can only roll back to the outermost level. But for implementing triggers and constraints, this should be all we need.
I don’t remember if that is the exception you receive when you have a nested transaction, but that could be in issue if it is, unless you can pull more information from the transaction error string.
Reason being, that is the same error you receive when you have multiple users calling the transaction function in parallel.
If they edit the same information, you will raise the conflict error and cause the loser of the race condition to essentially run their function without the transaction rule.
It would be nice to have a “NestedTransaction” Error instead. (maybe a feature request).
After what I highlighted in yellow it proceeds to describe the most common way that anvil uses python with transactions, so obviously it happens quite often.
I don’t think this is the way it is supposed to work, it is supposed to raise a transaction conflict within every called transaction, instead of a race condition.
It is the anvil wrapper that tries again up to 5 times, in-case the conflict was erroneous. If everything works within the rules of atomicity, all 10 conflicting transactions should fail and return with two transaction conflict errors, not one that succeeds and one error. (In the case of 2 transactions)
I agree it should raise the transaction conflict instead of the race condition, but if we used this wrapper as an example paired with the following:
@nested_transaction
def function():
other_function()
@nested_transaction
def other_function():
do stuff for a long time
function()
function()
either function() calls might raise the TransactionConflict, and because they are wrapped in the nested_transaction wrapper, they would just be called as normal methods without a transaction.
