Re: [sqlite] WAL, durability, and synchronous=NORMAL
Hi, >>> In WAL mode with synchronous=NORMAL, when the user commits >>> a transaction, it is written into the WAL file. No sync >>> until a checkpoint happens. So if the power fails, you might >>> lose all the transactions that have been written into the WAL >>> file. >> Ahha. That explains it. Thanks for that. This makes WAL mode less >> attractive to me. > If you use synchronous=FULL then it should not be possible to > lose a transaction once it has been committed (assuming the > hard disk is playing fair). Is there any way to force a sync to disk (e.g. that I could call every few seconds), other than PRAGMA wal_checkpoint ? Regards, Twylite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 09:04 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > >> In WAL mode with synchronous=NORMAL, when the user commits >> a transaction, it is written into the WAL file. No sync >> until a checkpoint happens. So if the power fails, you might >> lose all the transactions that have been written into the WAL >> file. > > Ahha. That explains it. Thanks for that. This makes WAL mode less > attractive to me. If you use synchronous=FULL then it should not be possible to lose a transaction once it has been committed (assuming the hard disk is playing fair). Of course, that means a sync at the end of each transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > In WAL mode with synchronous=NORMAL, when the user commits > a transaction, it is written into the WAL file. No sync > until a checkpoint happens. So if the power fails, you might > lose all the transactions that have been written into the WAL > file. Ahha. That explains it. Thanks for that. This makes WAL mode less attractive to me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 08:49 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > >> On 11/25/2010 03:24 PM, Twylite wrote: >>> > > Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't > like. > >>> I am seeking technical information on the durability of transactions >>> with journal_mode=WAL and synchronous=NORMAL. >>> >>> Specifically, in the event of a power failure, can the following ever >>> happen: >>> (1) Loss of the last transaction completed. >>> (2) Loss of some indeterminate number of recent transactions. >>> (3) Loss of the entire WAL file. >>> (4) Corrupt of the database making it unopenable. >> >> Possible. Possible. Possible. Not supposed to be possible. > > Is (2) really possible ? I thought that, unless the power failure caused > corruption at the hard disk level, a maximum of one transaction could be lost. > (2) and (3) are really the same question. In WAL mode with synchronous=NORMAL, when the user commits a transaction, it is written into the WAL file. No sync until a checkpoint happens. So if the power fails, you might lose all the transactions that have been written into the WAL file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > On 11/25/2010 03:24 PM, Twylite wrote: >> Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't like. >> I am seeking technical information on the durability of transactions >> with journal_mode=WAL and synchronous=NORMAL. >> >> Specifically, in the event of a power failure, can the following ever >> happen: >> (1) Loss of the last transaction completed. >> (2) Loss of some indeterminate number of recent transactions. >> (3) Loss of the entire WAL file. >> (4) Corrupt of the database making it unopenable. > > Possible. Possible. Possible. Not supposed to be possible. Is (2) really possible ? I thought that, unless the power failure caused corruption at the hard disk level, a maximum of one transaction could be lost. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 03:24 PM, Twylite wrote: > Hi, > > I am seeking technical information on the durability of transactions > with journal_mode=WAL and synchronous=NORMAL. > > Specifically, in the event of a power failure, can the following ever > happen: > (1) Loss of the last transaction completed. > (2) Loss of some indeterminate number of recent transactions. > (3) Loss of the entire WAL file. > (4) Corrupt of the database making it unopenable. Possible. Possible. Possible. Not supposed to be possible. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
