Re: [sqlite] Commit and rollback behaviour during power loss
There is one degenerate case, which has been discussed a few times on this list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that marks a transaction being committed is the deletion of the rollback journal. Deleting a file is a directory level operation, which means there are three files being changed: 1. The main DB file 2. The rollback journal 3. The directory By default sqlite fsyncs (1) and (2) as part of COMMIT, but not (3). This makes it possible for your program's control flow to get an SQLITE_OK result from "conn.commit()" but then lose power before the OS syncs the directory change to disk. As a result the transaction will be rolled back when the DB is next opened. It's a pretty extreme scenario, but you can avoid the risk via PRAGMA synchronous=EXTRA (to also sync the directory), or PRAGMA journal_mode=truncate (avoiding the need to modify the directory). I don't think there is any scenario in which power loss could result in multiple transactions being rolled back (unless fsync lies of course, in which case all bets are off). -Rowan On 24 November 2017 at 16:47, Blagovest Buyukliev < blagovest.buyukl...@gmail.com> wrote: > Let's say we have the following Python code: > > import sqlite3 > conn = sqlite3.connect('mydb.db') > c = conn.cursor() > c.execute("INSERT INTO ...") > conn.commit() > c.execute("INSERT INTO ...") > conn.commit() > > Can it be assumed that after conn.commit() has returned, fsync() has > been called on the file and (assuming that fsync doesn't lie and > doesn't just act as a barrier), the last inserted row has made its way > to disk? > > Also, say that power is lost during the *second* conn.commit(). Is > there a possibility that the database will be rolled back to the state > before the first INSERT, or is it guaranteed that it will be rolled > back to the state before the second INSERT? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Commit and rollback behaviour during power loss
> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk > with some caveats, namely: > > A - We are assuming the Python sqlite3 wrapper you use doesn't do > obfuscation of any sort and directly calls the sqlite3 API and returns the > direct result from those calls. If you haven't yet, consider using (or at > least trying) the APSW wrapper. Some inspection of the Python wrapper would reveal that. The important thing you note is that this condition can be distinguished from the C API. > B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite > has handed off the fsync() to the OS. Some OSes may lie about this having > been effected yet (in the interest of speed) and also, perhaps even > unbeknown to the OS, the hardware itself may lie about having actually > committed those bits to platters/NVRam/etc. Implementing a good database > system is as much about making the hardware comply as making the software > work. That said, data failures/corruption due to this B caveat is extremely > improbable and only ever happens in the weirdest of circumstances. The hardware and the OS are pretty much a fixed combination, since this will be used in an in-house hosted solution with Linux and some chosen enterprise-level storage. > [1] What I am not seeing in your code is conn.startTransaction() (or however > that will be named for you). How does the connection know you have meant for > a transaction to start so as to commit it later? I'm also not seeing you > testing any return value from those conn.commit() calls, but that might just > be for brevity of the post. They can however return a failure code, in which > case the commit didn't happen. Yes, the code is purely illustrative and lacks any error handling for brevity. > Again, if the first Commit returned with SQLITE_OK (with noting the above > caveats again), then no, that state is forever captured and part of the DB > now, and any new transaction will have its own atomicity. That's a very good thing which renders our potential solution viable. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Commit and rollback behaviour during power loss
On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote: Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and (assuming that fsync doesn't lie and doesn't just act as a barrier), the last inserted row has made its way to disk? Probably not[1], but lets assume for a moment a transaction was started successfully, then: If Commit returns with SQLITE_OK, then YES, it is handed off to the disk with some caveats, namely: A - We are assuming the Python sqlite3 wrapper you use doesn't do obfuscation of any sort and directly calls the sqlite3 API and returns the direct result from those calls. If you haven't yet, consider using (or at least trying) the APSW wrapper. B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite has handed off the fsync() to the OS. Some OSes may lie about this having been effected yet (in the interest of speed) and also, perhaps even unbeknown to the OS, the hardware itself may lie about having actually committed those bits to platters/NVRam/etc. Implementing a good database system is as much about making the hardware comply as making the software work. That said, data failures/corruption due to this B caveat is extremely improbable and only ever happens in the weirdest of circumstances. Also, say that power is lost during the *second* conn.commit(). Is there a possibility that the database will be rolled back to the state before the first INSERT, or is it guaranteed that it will be rolled back to the state before the second INSERT? Again, if the first Commit returned with SQLITE_OK (with noting the above caveats again), then no, that state is forever captured and part of the DB now, and any new transaction will have its own atomicity. [1] What I am not seeing in your code is conn.startTransaction() (or however that will be named for you). How does the connection know you have meant for a transaction to start so as to commit it later? I'm also not seeing you testing any return value from those conn.commit() calls, but that might just be for brevity of the post. They can however return a failure code, in which case the commit didn't happen. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Commit and rollback behaviour during power loss
Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and (assuming that fsync doesn't lie and doesn't just act as a barrier), the last inserted row has made its way to disk? Also, say that power is lost during the *second* conn.commit(). Is there a possibility that the database will be rolled back to the state before the first INSERT, or is it guaranteed that it will be rolled back to the state before the second INSERT? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users