Pseudo-Nested Database Transaction

Anvil Q&A
1

I have a server function that makes multiple changes to the db; all of which should be done within a single transaction.

However, it could be called multiple times from an outer function, in which case, the whole lot should be done within a single transaction.

I know Postgresql doesn’t support nested transactions, so I’ve come up with a solution using contextlib.nullcontext but I wondered if anyone has a better idea?

import anvil.sever
import contextlib

@anvil.server.callable
def load_many_things(iterable_thing):
    with anvil.tables.Transaction() as transaction:
        for item in iterable_thing:
            store_single_item(item, transaction)

@anvil.server.callable
def store_single_item(item, transaction=None):
    if transaction is None:
        txn = anvil.tables.Transaction()
    else:
        txn = contextlib.nullcontext()

    with txn:
        app_tables.some_table.add_row(item=item)
        app_tables.some_other_table.add_row(item=item)

** UPDATE **

Based on the suggestion from @stefano.menci, here’s another (cleaner) solution:

import anvil.tables
from tables import app_tables

def add_database_records(item):
    app_tables.some_table.add_row(item=item)
    app_tables.some_other_table.add_row(item=item)

@anvil.server.callable
@anvil.tables.in_transaction
def load_many_things(iterable_thing):
    for item in iterable_thing:
        add_database_records(item)

@anvil.server.callable
@anvil.tables.in_transaction
def store_single_item(item):
    add_database_records(item)
2 Likes
2

I would use three functions, two callable, both starting the transaction and both calling the third function. The writing to the db happens on the third function.

2 Likes
3

Postgres (and SQLite, and others) do support Savepoints within a Transaction. For practical purposes, these are nested transactions, and one of my C++ libraries treats them as such, so it can be done.

4

@p.colbert I have tried using savepoints in the past, but I ended up with code that was difficult to maintain and error prone. If I remember, using endpoints still requires one transaction start and one end, so you still risk to start another transaction or to end the transaction twice.

When I split the code into smaller functions I find it easier to create worker functions that work on the tables without worrying about transactions and http endpoints / callable functions that decide whether the worker functions should be wrapped in a transaction or not.

5

In the C++ library, open transactions are tracked, so that if a nested transaction is attempted, then the “nested transaction” is automatically translated into a savepoint. This “transaction stack” can go as deep as you like.

By factoring out this behavior into a “transaction wrapper” layer, callers did not need to track the distinction themselves, thus greatly simplifying the callers. They did not have to care whether the “transaction” they were creating was nested, or not. The translation layer (class, object) did that for them.

This did require using the translation layer consistently, so that nesting was being tracked reliably.

The Python equivalent would be a custom Context Manager, and would use a “with” statement to start and end a transaction. But unless Anvil starts supporting nested transactions, your custom Context Manager would have to use SQL more directly.

6

I like the C++ library that you are describing!
My experience is limited to Python + SQLite + Pewee, which doesn’t have nested transactions, so I made my own context manager to do something similar to your approach (see comments here).

Another note I would like to add to this thread is that with Anvil I use the Anvil app_tables library most of the times, but when I need some fancy SQL query I use psycopg2. So I need to always keep in mind that:

  1. when I use psycopg2 I need to manage the transaction using SQL statements
  2. when I use app_tables I need to manage the transactions using Anvil tools
  3. if I mix psycopg2 and app_tables on the same call I end up with two connections, each with its own transaction (can be dangerous!)
  4. avoiding problem 3 is impossible if you use psycopg2 for the queries and Anvil to manage user permissions; this is usually not a problem, but it’s good to keep in mind
1 Like