> Please, give me some examples of the kinds of things you are
> doing which could benefit from improved concurrency.

One typical application for me is data recording for regulatory
compliance (FDA 21 CFR 11). Instruments are polled or issue data
frequently, say once a second. Data from several of these instruments
are collected during the sample period, timestamped and digitally
signed or hashed, and all of this is updated in the database in a
single transaction. While this is going on, a few other things may be
taking place as well: periodic data selects for user display refresh,
data updates for user configurations or calibrations, and ad hoc query
by regulators or users.

>    *  What SQL are you running that takes more than a fraction
>       of a second to complete?

Ad hoc queries can be troublesome since the database can get really
big after several months. It's very important that the instrument data
update is (and to only a slightly lesser degree display refresh and
config updates are) not blocked too long.

>    *  Are you holding transactions open for an extended period
>       of time?  Why?

No.

>    *  How many processes do you have trying to access the database
>       at once?

One would be fine. A separate pipe to an ODBC (read only) process
would be nice, though, for ad hoc reports. Presently I manage this
pipe within my process because...

>    *  How do you currently handle SQLITE_BUSY replies?  Do you use
>       the sqlite_busy_handler() or sqlite_busy_timeout() APIs?

Various strategies have been tried, but in the end, and due to the
coarse grained locking, I ended up serializing access to SQLite in my
own uni-process multi-thread bottleneck.

>    *  How large are your databases?

They can grow to multiple G bytes; typically hundreds of M bytes.

>    *  Do you ever put database files on a shared filesystem?

No.

> The better I understand the problems, the better job I will
> be able to do in resolving them.  Thanks for you responses.

Thank you.

As you can see from my description, I can make SQLite work, but there
are risks associated with ad hoc queries. An ideal architecture for me
would be "readers never block writers." MVCC does this, but I suspect
it is too big a change for SQLite. Finer grained locking reduces the
risk, but not to zero. With some kinds of fine grained locking I can
perhaps devise schemas that control the risks.

e


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to