No atomicity across two databases

A change that touches the system DB and a tenant DB is not atomic. This page explains when that happens and what it means.

Variants

normal request: one databasehook or custom code writes both levelserror before the commit → both rolled backerror during the commit → partial state possibleoperating mode SINGLE

What this is about

Atomic means: all or nothing. Within one database, a request is atomic, see One request, one transaction.

But CDMS knows two kinds of databases: the system database for models of the system level and the tenant’s database for models of the tenant and user levels. See Model levels: system, tenant, user. Each database has its own transaction. There is no bracket that commits both together.

When a request touches two databases

Through the generated endpoints this does not happen: relations only exist between models of the same database, so nested writing also stays within one database.

Two databases only come into play through custom code:

a hook on a tenant model that additionally writes a system model, such as a counter or a log entry for the whole installation,
or the other way round, a hook on a system model that writes tenant data,
a custom endpoint that changes both levels.

flowchart LR
    C["Client"] --> D["CDMS: one request"]
    D --> T1["Transaction 1"] --> SYS[("System database")]
    D --> T2["Transaction 2"] --> MAND[("Database tenant A")]

What happens on errors

A request writes to both databases

When does the error occur?	Result
not at all	both transactions are committed, 2xx
during processing: role, validation, hook, database rule at flush	both are rolled back, nothing remains
during the commit of the first database	nothing committed, error response
during the commit of the second database	the first one is already committed, the second one is not; error response

So the partial state only arises in a narrow window: everything is checked and written, and then committing the second database fails, for example because it is not reachable at that moment. This is rare, but possible, and the order of the two commits is not fixed.

1

CDMS
processing successful, both databases have open changes
2

CDMS→Database
commit of the system database succeeds
3

CDMS→Database
commit of the tenant database fails
4

CDMS→Client
error response; the tenant change is rolled back

Result: The system database contains the change, the tenant database does not.

Operating mode SINGLE

In the operating mode SINGLE, everything lives in one database. Still, CDMS writes system models and tenant models of one request in separate transactions. The rules above therefore apply in both operating modes. See SINGLE and MULTI across both modules.

How to deal with it

Three rules for custom code

Avoid

one business operation writes one level
data that belongs together belongs on the same level

Choose the order

if it cannot be avoided, split it into two requests
first the side whose partial state is harmless
then the second; if it fails, only something harmless remains

Make it detectable

a partial state must be findable
e.g. through a status like pending that only turns into done in the second step
or a compensation step that cleans it up later

Pitfalls

What comes next

The bracket within one database: One request, one transaction
Which data lives in which database: Model levels: system, tenant, user
The second boundary of the transaction: Files and transaction