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 ?
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
