> Shutting down power right after a successfully committed > transaction rolls back that transaction on next startup.
nitpick: This is sqlite behaving as advertised. See https://www.sqlite.org/lockingv3.html section 5.0 step 6, and https://www.sqlite.org/atomiccommit.html section 3.11 which explain that the presence of a [well-formed] journal file is the mechanism via which sqlite discriminates between a committed and in-progress transaction. ie. according to sqlite a transaction is *not* successfully committed if the journal file is still present, so its well within its rights to rollback in this scenario. That said, syncing the directory doesn't sound like a terrible idea. But it's not clear to me that the cost of another sync every transaction is worth a marginal reduction in the power-failure-leads-to-rollback window. That's if it even reduces the window; it wouldn't surprise me to find that the journal is removed from disk after the same delay both with and without dirsync, the difference being that the dirsync prevents sqlite from returning control to your code until its certain the commit has persisted. There's certainly a surprising result here: if (sqlite3_exec("COMMIT") == SQLITE_OK) { /* post-commit logic */ } Logically, the code in the if block can reasonably assume that the transaction will not rollback. But as you have discovered this is not always true with JOURNAL_MODE=DELETE unless the dirsync is performed. To be fair I don't think there are many power-failure scenarios where the post-commit logic would have a chance to do anything significant, so the incorrect assumption will usually be moot. In your case it sounds like a controlled shutdown - is there a reason you don't do a full disk sync before that? -Rowan On 19 January 2016 at 21:33, Meinlschmidt Stefan < Stefan.Meinlschmidt at esolutions.de> wrote: > Hi everybody! > > TL;DR: > > Shutting down power right after a successfully committed > transaction rolls back that transaction on next startup. This is a > problem in write-on-shutdown-then-power-off scenarios and violates my > expectation of SQLite's transactions being ACID. This can be fixed by > setting the dirSync-flag of the sqlite3OsDelete-call within > pager_end_transaction. > >
