Database schema migration is unreliable on sqlite due to lack of transactional safety

[richvdh]

python's sqlite library does not open a transaction when cursor.execute() is called for data definition statements (CREATE, ALTER, etc). (ref: "The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).")

That means that when we do a schema migration, the first delta is normally committed immediately. However, the INSERT INTO applied_schema_deltas then starts a transaction, which isn't committed until all of the deltas are completed.

The upshot is that if a delta fails for some reason, we end up with the first delta having run, but not recorded in applied_schema_deltas. Subsequent attempts to start synapse will then re-run the delta, which will fail if the first delta file is not idempotent.

I think the whole thing would be much more reliable (in postgres too) if we explicitly wrapped each delta with a transaction.