Remix.run Logo
agwa 4 days ago

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.