Use database transactions

[jonasraoni]

Discussion
This has been converted into discussion from the issue: #10275 (PRs available).

We clearly need to enclose operations that deal with more than one entity within a transaction, otherwise the database might be left with half-baked data (which might break assumptions and raise exceptions) in case of bad handled failures/outages.

It's not needed to say that spreading transactions to the whole system will be a Herculean job, but there are other challenges. For example...
Given that we allow 3rd-party code to interact, it's not possible to assume that your single operation will really not have side-effects on other entities, then it might make sense to enclose everything in a transaction.
Going further over the same reasoning, if a process fails later, a plugin that did some operations, should be able to register itself for failure recovery.

Another important subject when dealing with transactions is the isolation level. Laravel doesn't setup a default transaction isolation, then things might behave differently depending on the default isolation set at the database configuration.

The default transaction isolation of MySQL is repeatable read, while PostgreSQL uses read committed. A sysadmin can also change the defaults, even though it's unlikely to happen, we shouldn't trust on it.

We should go with the read committed, it's more performatic (the database won't have to manage too much data/issue extra locks), represents better what's happening at the database, and by reducing the number of locks, the occurrence of deadlocks should also decrease.

The difference lies on the possibility of phantom reads, which is ok!

[jonasraoni]

While I was writing a discussion, I've noticed the isolation isn't stable. I'll update just the main branch. Anything against @asmecher?

[asmecher]

I'm ambivalent... I would prefer we hewed close to the defaults in general, and I don't think this solves a problem anyone's experienced.

[jonasraoni]

I think we need to enforce it for the sake of predictability. In theory changing the default of MySQL should improve the performance when under higher concurrency. The market standard is to use the read committed isolation (MySQL is an outlier).

In one of the discussions, I proposed an idea that would need a specific isolation to work properly (otherwise the idea would work fine in PostgreSQL, but not in MySQL).

[asmecher]

@jonasraoni, what's the feature that requires isolation?

[jonasraoni]

Ah, I've left a note on the "Drawbacks" of #10274. If the system is under a transaction, and you want to have another independent operation(s), then you need a separate transaction/connection. And if you want both to see each other committed changes, a read committed is needed.

IMHO the behavior of repeatable read produces a fake reality (the data is on the database, already committed, but you may or may not see it, depending on your previous queries) and it has extra costs/locks.

The other point that I care, is to have an environment as standard as possible. So, we should either use read committed or repeatable read, but not leave it to fate.

[asmecher]

We haven't started using transactions yet (and we're not ready to start just yet -- there's a relevant discussion in the Development channel in Mattermost from 2024-07-31) and #10274 isn't on the roadmap yet, so I don't think this change is motivated just yet. I am motivated by a reduction in locks, but that won't materialize until we start using transactions. Could you turn this into a discussion and scope it up to be a general capture on transactions?