It's specifically about fsyncing journal deletion. The problem isn't that it would reapply it if it was already used to rollback. Rather, the problem is that if you commit, and that commit has succeeded (and so your app believes that it has written the data and might e.g. perform some other actions on it), the deletion of the now-unneeded journal might not be flushed to disk in event of power loss or similar. So when you start the app again, SQLite sees said rollback journal, and - since it would be considered "hot" - applies it, effectively reverting the transaction that was supposedly already committed.

FWIW I don't think it's wrong per se. The article links to a HN comment in which Richard Hipp explains why this is the default behavior, and it does make sense: https://news.ycombinator.com/item?id=45014296. At the same time, clearly, the definition of "durable" here could use some clarification.

▲ agwa 4 days ago | parent | next [-]

Yes, that's exactly right.

Note that the comment by Richard Hipp is justifying why WAL mode is not durable by default. It's a completely reasonable explanation, and would be for DELETE mode too, yet his comment claims that DELETE mode is durable by default, which I can't reconcile with the docs.

▲ NewsaHackO 4 days ago | parent | prev [-]

>So when you start the app again, SQLite sees said rollback journal, and - since it would be considered "hot" - applies it, effectively reverting the transaction that was supposedly already committed.

Guys. The journal would not be a hot journal though, as the hot journal selection only applies if the database is in a inconsistent state. Otherwise, the database knows from the ID of the journal not to reapply an already applied rollback journal. The process you are talking about ONLY happens when the journal database has been corrupted state, and it has to try and file a file to help recover the database.

▲ agwa 4 days ago | parent | next [-]

OK, I just tested it:

In terminal 1, I created a database and added a table to it:

  $ sqlite3 testdb
  sqlite> create table test (col int);

In terminal 2, I attached gdb to sqlite3 and set a breakpoint on unlink:

  $ gdb sqlite3 `pidof sqlite3`
  (gdb) b unlink
  (gdb) c

Back in terminal 1, I inserted data into the table:

  sqlite> insert into test values(123);

In terminal 3, I saved a copy of testdb-journal:

  $ cp testdb-journal testdb-journal.save

Then in terminal 2, I resumed executing sqlite3:

  (gdb) c

In terminal 1, the INSERT completed without error.

Back in terminal 3, I sent SIGKILL to sqlite3, simulating a power failure:

  $ killall -9 sqlite3

I then restored testdb-journal, simulating what could happen after a power failure when the parent directory is not fsynced:

  $ mv testdb-journal.save testdb-journal

I then opened testdb again and ran `SELECT * FROM test` and it returned zero rows.

This proves int_19h and I are right - if the journal file comes back, SQLite will apply it and roll back a committed transaction.

I then confirmed with strace that, as the documentation says, the directory is only fsynced after unlink when synchronous=EXTRA. It doesn't happen with synchronous=FULL. So you need synchronous=EXTRA to get durability in DELETE mode.

▲ agwa 4 days ago | parent | prev | next [-]

The docs list 5 conditions that all must be satisfied for the journal to be considered hot: https://www.sqlite.org/atomiccommit.html#_hot_rollback_journ...

I believe they would all be satisfied.

I don't see any mention of checking IDs. Not saying you're wrong - I think the docs could very well be wrong - but could you provide a citation for that behavior?

▲

NewsaHackO 4 days ago | parent [-]

Please read the entire document instead of just picking out sections; you will then be able to see where your misconceptions are occurring. You have attempted to make this same point three times, so I will say it for a third time; that section is about CORRUPTED databases, not database that are consistent after fsync.

	▲	aktiur 4 days ago \| parent \| next [-]
		The text in this document also directly contradicts what you're saying. Put another way: the presence of a hot journal is how SQLite determines the database might be corrupted. https://sqlite.org/lockingv3.html#hot_journals
	▲	agwa 4 days ago \| parent \| prev [-]
		I read the whole document. It doesn't mention IDs anywhere. If you're not going to provide citations for your claims, yet criticize me for "picking out sections" when I provide citations, then continuing this conversation won't be productive.

▲ int_19h 4 days ago | parent | prev [-]

> Otherwise, the database knows from the ID of the journal not to reapply an already applied rollback journal.

But it's not "already applied", that's the whole point. The transaction was committed, not rolled back, so the changes in transaction were persisted to disk and the journal was just thrown away. If it magically reappears again, how is SQLite supposed to know that it needs to be discarded again rather than applied to revert the change?