SQLite Forum

Transactions involving multiple databases

Transactions involving multiple databases

(1) By KlausenLXXI on 2020-12-10 15:53:07 [link] [source]

I am about to start using SQLite for maintaining in an application. For this, I am trying to determine if I should use one big database or if I should use multiple databases.

After having considered different Pro's and Con's, I have determined that multiple databases is preferred.
However, in some cases I will then need to be able to make a change in two databases within the same transaction - and it is especially important to me if the application crashes, in case of power failure or other scenarios where the application is not stopped correctly.

Here I found it described, that it should be possible (I think).
The page says:
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."

The first sentence tells me, that I can do this, as I am not using :memory: and I can probably do without WAL.

However, it is unclear to me what the last sentence means:
"But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."

Is this only if using :memory: or WAL or is this always the case (which sort of means the transactions using multiple database are not atomic)?

I assume that SQLite does something other, than just a simple commit in db1 and then in db2 as I could obviously do that myself - but I would really like to know how much I can rely on transactions involving two databases - so that either the change is made to both databases, or it it not made to any of them?

(2.1) By Keith Medcalf (kmedcalf) on 2020-12-10 16:23:07 edited from 2.0 in reply to 1 [link] [source]

It should be interpreted as if the ". But" were not separate sentences but the part following "but" were a correlated subjunctive even though in English Grammar such construction such would usually be indicated with the period being replaced with a semi-colon (";"), the But not being capitalized and followed by a comma (",").

In other words, in the case where the journal_mode is not WAL (that is, for the greater certainty, is not one of the persistent rollback modes) and the main database (and the attached database) is not :memory: (which are non-persistent) THEN two-phase commit is used to maintain consistency across attached database files.

However, if there conditions are NOT complied with (that is, one of the databases involved is a :memory: database, or the main database is a :memory: database, or the journal mode is not rollback using rollback files on persistent (disk) storage that two-phase commit WILL NOT BE USED however each individual database will still respect atomicity of transactions in accordance with the settings applicable to that database file.

** Edited to fix bad punctuation

(4) By KlausenLXXI on 2020-12-10 22:06:33 in reply to 2.1 [link] [source]

Thank you very much - that was pretty clear. I had tried to search SQLite.org for "two phase commit", but I couldn't find any mentioning of whether it was used/supported or not.

Regarding the English Grammar - I am definitely not an expert there, but certainly ".., but .." or "..; but, .." would not have confused me in this case.

(3) By David Raymond (dvdraymond) on 2020-12-10 17:23:43 in reply to 1 [source]

I recommend going through the Atomic Commit In SQLite page. Section 5 is the Multi-file commit section.

(5) By KlausenLXXI on 2020-12-10 22:07:36 in reply to 3 [link] [source]

Thank you - I will read the article.