Re: [sqlite] mj file loss consequences

[Brett Mcdonald]

Understood and most grateful for the quick response.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mj file loss consequences

[Richard Hipp]

On Wed, Feb 5, 2014 at 9:03 AM, Brett Mcdonald  wrote:

> >The only consequence of losing -mj file is that in a multi-database
> >transaction (which can only be in rollback mode, not in WAL mode) if you
> >lose power and come back up without the -mj file, then the transaction
> >might commit to some of the database files, but not to others.
>
> Are you only stating that an -mj file cannot support transaction
> rollback across WAL dbs in a recovery situation (e.g power failure), or are
> you also stating that cross database atomic behavior cannot be supported
> for WAL databases, period?
>

See disadvantage #3 at http://www.sqlite.org/wal.html

If the process doing the COMMIT is killed off (for example using "kill -9")
in the middle of the COMMIT, then after recovery the WAL database might see
the commit and the RAM-disk database might not.  Or it might be the other
way around.  Usually they would both either commit or both rollback.  But
there is a very narrow window of vulnerability, during which the death of
the committing process might cause only one or the other of the databases
to commit.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mj file loss consequences

[Brett Mcdonald]

>The only consequence of losing -mj file is that in a multi-database
>transaction (which can only be in rollback mode, not in WAL mode) if you
>lose power and come back up without the -mj file, then the transaction
>might commit to some of the database files, but not to others.

Are you only stating that an -mj file cannot support transaction
rollback across WAL dbs in a recovery situation (e.g power failure), or are
you also stating that cross database atomic behavior cannot be supported
for WAL databases, period?  Surely the ram db's connection (that attached
the WAL db and begun the transaction) also 'transacts' (begin, cancel,
commit) changes made to the WAL db on that very same connection?  Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mj file loss consequences

[Richard Hipp]

On Wed, Feb 5, 2014 at 7:50 AM, Brett Mcdonald  wrote:

> What are the recovery consequence(s) to a database (journal mode wal)
> should a master-journal file be lost?
>
> Suppose a flash sensitive embedded product had to update its database every
> second.  To save flash wear the database (journal mode delete) is created
> in ram (e.g. /tmp).  Suppose every 8 seconds there had to be a transaction
> across the ram database to not only update it but also to sync with another
> database in flash (journal mode wal).  Both databases must be updated and
> sync'd atomically, a.k.a one transaction.  Flash cannot afford to have an
> mj file created/deleted every 8 seconds.  So, assuming...
>
> -The ram db connection attaches the flash db
> -Transaction is started and mj file created in ram
> -Ram and flash databases are modified
> -On power failure the flash db's wal file is flushed (persists reboot)
> however the mj transaction never commits and the ram db and mj file are
> gone (they're in ram after all).
>
> Ideally, on reboot the flash database will recover via its wal file, the
> ram db will be recreated and back in business.
>
> Obviously the product can afford to lose the ram db and the previous 8
> seconds of data, but does losing the ram db with the corresponding mj file
> have recovery consequences for the flash db and its wal file?
>

The on-disk database file will be unharmed.

The only consequence of losing -mj file is that in a multi-database
transaction (which can only be in rollback mode, not in WAL mode) if you
lose power and come back up without the -mj file, then the transaction
might commit to some of the database files, but not to others.  In other
words you use cross-database atomicity.  But updates to each individual
database file are all still atomic.

In your case, since one of the two databases is in RAM and is lost
completely, it doesn't really matter if it is in sync with the on-disk
database.  The change to the on-disk database is atomic.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users