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
