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