On Sat, Nov 5, 2011 at 2:56 PM, swamir <sw...@infineta.com> wrote: > > Hi, > Using sqlite version 3.6.x, so WAL mode not available. Need to see if i > need to upgrade to 3.7.x. > > By the way, in WAL mode, i understand that whenever the WAL file reaches > 1000 page of changes(which is the default), only then checkpoint/write > happens to the real database file . How will it handle when system is > restarted while transaction is in progress ? >
If an incomplete transaction is partially written to the WAL file prior to a crash, then after the crash, the incomplete transaction is simply ignored. The has the effect of rolling back the incomplete transaction. > > > Regards > swami > > > Jean-Christophe Deschamps-3 wrote: > > > > > >>Is there a good practice /setting for such high rate of concurrent writes > >>with reads ? > >>I understand that multiple readers with single writer at any time t is > the > >>model which sqlite has with various locks/states (unlocked, pending , > >>shared > >>, reserved , exclusive). > > > > "with" is a bit unclear. > > > > To clarify, unless you're using WAL mode, there in no actual > > concurrency in SQLite. > > Quoting Richard Hipp who recently wrote: > > > >>(1) You only have to set WAL mode once for the database. The database > >>remembers that it is in WAL mode and all subsequent opens bring it back > up > >>in WAL mode again (until you deliberately change it out of WAL mode.) You > >>do *not* have to set WAL mode every time you open the database file. > >> > >>(2) WAL really does give better concurrency since it allows other > >>processes > >>to continue reading while a single process is writing to the database. In > >>rollback mode you can have one writer *or* multiple readers. In WAL mode > >>you can have one writer *and* multiple readers. On a busy system, or in a > >>system with long-running transactions, the difference can be significant. > >> > >>(3) Transactions commit a lot faster with WAL. Depending on your app, you > >>might see a measurable performance improvement just by switching to WAL. > >> > >>(4) WAL mode is much more resistant to corruption following a power > >>failure > >>that occurs soon after the disk drive lied and said that content was > >>committed to oxide when in fact it was still in a volatile track buffer. > >>And pretty much all consumer-grade disk drives tell such lies these days. > > > > The part relevant to you is in (2). > > rollback mode: 1 writer OR many readers > > WAL mode: 1 writer AND many readers > > > >>Will a busy_timeout setting for all connections and making write > >>transactions as "begin immediate" handle the situation ? > > > > Yes, try using the same timeout for _all_ conections and always use > > IMMEDIATE transactions for RMW (Read Modify Write) SQL blocks, just > > BEGIN for read-only SQL blocks and autocommit for stand-alone statements. > > > > You have to decide on the value of the timeout, and the ideal value in > > rollback mode is NOT the time it takes for the worst case transaction > > plus some margin. > > > > To see why look at how SQLite behaves when your transaction (A) is > > locked by a blocking transaction (B): it retries internally a certain > > number of times for a short period, then puts (A) to sleep for a > > while. During this short sleep (B) may terminate and another blocking > > transaction (C) may proceed. When (A) gets out of sleep it still sees > > the DB locked (this time by (C) not by (B) but noone notices). This > > ends with either: the longest sequence of the worst-case transactions > > terminates and your (A) proceeds normally OR your timeout expires and > > you get "DB locked". > > > > WAL dramatically simplifies this that since readers can by concurrent > > to one writer: the right timeout is actually the worst-case write > > transaction plus some margin. > > > > I've been using this setup (with 10 minutes timeout, that's close to > > forever!) for years in my business-type applications and never got any > > issue. (I'm currently switching to WAL mode.) > > > > Please someone corrects me if this doesn't reflect SQLite current > > behavior anymore. > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://old.nabble.com/concurrent-writes-and-reads-to--from-DB-tp32784852p32787541.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
