3 ms·

Using subtransactions appropriately is unlikely to trigger any noticeable performance differences, any more than 'having less transactions' overall will. When

Using subtransactions appropriately is unlikely to trigger any noticeable performance differences, any more than 'having less transactions' overall will. When doing multiple steps that need to track external state, subtransactions can greatly simplify the schema you need as well as make the database interactions more efficient. You need to safely record progress at each step, and you can't afford to lose previous steps because a transaction gets rolled back unexpectedly. You could start a transaction and commit between each step, but this gets pretty annoying if you need to SELECT FOR UPDATE or otherwise make sure what you are doing is concurrency safe, since this would require you to track all the resources you need as you go, which would greatly complicate the design of your non-sql code. Not to mention the added failure modes of not being able to reacquire those resources after step N for whatever reason. Where things get stupid is ORMs and frameworks that start sub transactions all over the place when you don't need them and aren't even trying to use them. I'm looking at you, Django transaction.atomic(), you absolute idiot.

samokhvalov 5y ago
What does "appropriately" mean in this context?
- ltbarcly3 5y ago
  Not automatically inserting subtransactions that aren't necessary, especially when you aren't setting your application up to ever actually use them, as a side effect of something else. Like Django's transaction.atomic(), which at the top nesting level starts and commits a transaction, but which any dynamically nested calls start and commit sub transactions. This is almost never what anybody wants, I can count on one hand the number of times I've seen code that handles failed sub transactions and continues the overall transaction, and which most programmers don't even know how it works or what it does.
SPBS 5y ago
> When doing multiple steps that need to track external state Do you have an example of this? Many explanations for SAVEPOINT say it is useful when 'recalculation is deemed too expensive' but I cannot come up with a satisfactory example where this is truly the case.

samokhvalov 5y ago

What does "appropriately" mean in this context?

ltbarcly3 5y ago
Not automatically inserting subtransactions that aren't necessary, especially when you aren't setting your application up to ever actually use them, as a side effect of something else. Like Django's transaction.atomic(), which at the top nesting level starts and commits a transaction, but which any dynamically nested calls start and commit sub transactions. This is almost never what anybody wants, I can count on one hand the number of times I've seen code that handles failed sub transactions and continues the overall transaction, and which most programmers don't even know how it works or what it does.

SPBS 5y ago

> When doing multiple steps that need to track external state Do you have an example of this? Many explanations for SAVEPOINT say it is useful when 'recalculation is deemed too expensive' but I cannot come up with a satisfactory example where this is truly the case.