Schema deltas are no longer transactional on sqlite

[squahtx]

#13873 may have introduced a regression where schema deltas are no longer executed inside a transaction on sqlite. This can lead to database inconsistencies and bootloops when Synapse is killed while running database migrations.

Examples:

• [sqlite] can't launch after upgrade due to failing database migration #14333
• Intermittent sqlite3.OperationalError: table background_updates already exists on startup #14460

Previously: #6467.
Split out from #14401.

[squahtx]

When using sqlite, schema deltas are not fully transactional (see docstring here). The schema delta will end up being half applied and the database will end up in an inconsistent state. Depending on the nature of the schema delta, the next run may fail.

It looks like this may have been introduced by #13873, which replaced the old handrolled code for parsing sql files with something more conventional. That was probably a good idea, but it does break the transactional semantics we were relying on for our migrations...

Originally posted by @richvdh in #14401 (comment)

[squahtx]

When using sqlite, schema deltas are not fully transactional (see docstring here). The schema delta will end up being half applied and the database will end up in an inconsistent state. Depending on the nature of the schema delta, the next run may fail.

It looks like this may have been introduced by #13873, which replaced the old handrolled code for parsing sql files with something more conventional. That was probably a good idea, but it does break the transactional semantics we were relying on for our migrations...

Sorry, that's my mistake. FWIW the old parser still exists, see e.g.

synapse/synapse/storage/prepare_database.py

Line 609 in 0a38c7e

def get_statements(f: Iterable[str]) -> Generator[str, None, None]:

The only other option would be to inject an explicit BEGIN TRANSACTION and COMMIT before and after the script. But that's not a like-for-like replacement for what had before. Maybe it's best to revert back.

(I recall that when I was making the full schema dumps that parser couldn't handle something postgres-specific. Trigger definitions maybe?)

Originally posted by @DMRobertson in #14401 (comment)

[squahtx]

The only other option would be to inject an explicit BEGIN TRANSACTION and COMMIT before and after the script. But that's not a like-for-like replacement for what had before

It's not, but I think it would have the right semantics, and would avoid us maintaining our own SQL parser, with all the pitfalls that brings.

Originally posted by @richvdh in #14401 (comment)

[squahtx]

@DMRobertson points out here that there is a small window in between the schema delta being committed and Synapse updating its bookkeeping which is still non-transactional.

We need to leave the transaction open in executescript, if that is permitted, or move the bookkeeping update into the sql that gets run.