What is the Journal file on Android

Question

When I created a database in Sqlite on Android, I noticed a file with the table name, followed by -Journal. What is it and what it’s for?

Strange this file have different permissions and use a table icon in an explorer application...

Answer 1

The purpose of a Journal ("") is to ensure the atomicity changes made to the database data: say you have a transaction with 3 Inserts/updates/Letes; how to ensure that or all three succeed or the three of you fail? How to ensure that, for example, you do not subtract funds from one bank account and, before adding these funds to the other, the computer stops and those funds disappear?

This is done through a special file, which records all the transactions that are being carried out until the moment of its completion. An example (fictitious) would be:

iniciou transação
fez insert na tabela X com os valores A
fez insert na tabela Y com os valores B
fez update na tabela Z com os valores C; os valores antigos eram D
commit

When you start the transaction, the bank is in its original state; when you do the first insert, it stores the new data in a temporary region (example; each bank can do it in a different way); idem pro segundo; idem pro pro update; when you commit, it takes the data from the first insert and put on the final table; idem pro segundo and pro update; once ready, it erases the Journal.

What’s this all about? Simple:

• If the computer crashes before the commit, when rebooting the bank reads the Journal, it sees that there was no commit, and throws away the entire transaction;
• If the computer crashes after the commit, when rebooting the database reads the Journal, see if each individual transaction has already had its data moved to the definitive table, and if it does not have it completes the operation;
  • If the computer crashes again while he’s doing it, the next time he calls start all over again...
  • When he finally manages to finish the transaction, only then will he erase the Journal.
• If the computer crashes after a rollback, when rebooting the bank reads the Journal, see if each individual transaction has ever been undone (does not apply to our example, where changes are only confirmed at the end, but other strategies may require it), and if it was not it completes the operation;
  • If the computer crashes again while he’s doing it...
  • At the end, delete the Journal.
• If the computer crashes when the Journal is empty/absent, when rewiring he knows that there is no transaction "halfway", so he can continue in a good.

Note that if there is no Journal on file, the atomicity of any transaction cannot be guaranteed! In the question indicated by Pope Charlie in the comments AP asks for a way to disable it (i.e. to cause the Journal exists only in memory / does not exist at all). If you don’t have a good reason to do this, don’t: otherwise, your transactions may be incomplete (unless of course you don’t have any complex transactions that require atomicity).

And if I haven’t been clear, this is even true for "transactions" with a single operation (I’m uncertain about operations out of of a transaction, but for security I would assume that is also the case). Quoting the documentation of Sqlite:

The mode of journaling MEMORY keeps the diary of rollbacks in volatile RAM. This saves disk I/O but at the expense of database security and integrity. If an application using Sqlite hangs in the middle of a transaction when mode MEMORY is active, then probably the database file will get corrupted.

The mode of journaling OFF disables the journal of rollbacks completely. No journal is created and therefore there is never a file to remove. (...) If an application using Sqlite hangs in the middle of a transaction when mode OFF is active, then probably the database file will get corrupted.

Additional references:

• That my answer to the question "What is a Mysql Transaction for?"
• That answer to my question "How to implement journaling in Python?"