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
