Re: [sqlite] synchronization issue: no directory sync after unlink
AFAIK, before deleting journal file SQLite writes some zeroed header into it. This kind of header means that transaction is finished. And even if after power shutdown SQLite finds this journal persisting on disk it will see the header and will understand that this journal just needs to be deleted. So what important here is sync after writing zeroed header, not after deletion. Directory sync is important only when journal is created, so that it doesn't suddenly disappear when power shuts off. You can check in strace whether what I said above is actually true. Pavel On Thu, Feb 23, 2012 at 6:02 AM, Florent Bayendrian yerva...@gmail.com wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] synchronization issue: no directory sync after unlink
The header is overwritten with some zeros only if I change the journal_mode to PERSIST : http://www.sqlite.org/pragma.html#pragma_journal_mode By default, the journal_mode is set to DELETE so the journal file is valid if the file is present after an unlink + power shutdown. (I have checked with strace and with an exit just before the unlink) BR, Florent On Feb 23, 5:34 pm, Pavel Ivanov paiva...@gmail.com wrote: AFAIK, before deleting journal file SQLite writes some zeroed header into it. This kind of header means that transaction is finished. And even if after power shutdown SQLite finds this journal persisting on disk it will see the header and will understand that this journal just needs to be deleted. So what important here is sync after writing zeroed header, not after deletion. Directory sync is important only when journal is created, so that it doesn't suddenly disappear when power shuts off. You can check in strace whether what I said above is actually true. Pavel On Thu, Feb 23, 2012 at 6:02 AM,FlorentBayendrian yerva...@gmail.com wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ sqlite-users mailing list sqlite-us...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] synchronization issue: no directory sync after unlink
On 02/23/2012 06:02 PM, Florent Bayendrian wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. You're correct. The way things are now, if you commit a transaction and then the power fails very quickly afterwards, following a reboot you may find that your transaction has been rolled back. And the way to fix it would be to sync the directory following the unlink() as part of the commit. The downside is, of course, that that extra sync will slow down all transactions committed using PRAGMA journal_mode=DELETE. With smallish transactions, it would be reasonable to assume that the overhead might be somewhere around 20-30%. Since there is no risk of database corruption, only transaction rollback following an unlucky power failure, we figure that the extra durability is not worth the performance cost. Easiest workaround would be to use either journal_mode=PERSIST or journal_mode=WAL. Or to create a VFS shim that makes sure the syncDir flag is set on all calls to xDelete. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] synchronization issue: no directory sync after unlink
Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
