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
