Hi - I wanted to answer some of the survey questions about concurrency in SQLite.
Finer-grained locking would not help our particular application. We have a multi-threaded application where there are many reader threads and a single writer thread. It's not a big deal for us if the readers read uncommitted data. A pragma to allow 'dirty' reads in a read-only connection would really help concurrency in our case. My favorite features of SQLite are the small size, code portability, small memory footprint, and that it is easily embedded. (Plus the source code is some of the best I've ever seen from a commenting and clarity standpoint.) With some minor tuning of the DB parameters, our schema and queries, we've been able to make it fast enough for our needs, so I'd be sad to see changes in database locking make it harder to embed the database or make it less portable. As for the questions: > * What SQL are you running that takes more than a fraction of a second to complete? We have a number of queries that take a few seconds to run. They tend to involve tables with 100,000 or more rows, where the query criteria matches about 1000 of them, sorts and returns, say, the most recent 50 of those 1000 rows, via an SQL LIMIT clause. As a non-sequiter, we experimented with a number of btree page sizes and we found 4k to be the fastest for our particular application. It seems that modern OS's can read 4k in about the same amount of time that they can read 1k from disk. > * Are you holding transactions open for an extended period of time? Why? We do hold transactions open for an extended period of time - our application acquires data from disparate sources, many of them over the net and stores metadata in the database. To make inserting a large number of rows faster, we wrap them in a transaction. We haven't experimented yet with committing periodically to make the application more live while it is gathering data. > * How many processes do you have trying to access the database at once? One process with multiple threads. We have one writer thread and any number of reader threads. > * How do you currently handle SQLITE_BUSY replies? Do you use the > sqlite_busy_handler() or sqlite_busy_timeout() APIs? We use the sqlite_busy_handler() APIs. > * How large are your databases? In the hundreds of megabytes. > * Do you ever put database files on a shared filesystem? No. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
