Daniel Watrous wrote: > I've developed an application that has very high concurrency. In my > initial testing we used SQLite 3 from python, but we experienced too > many locks and the database always fell behind.
What precisely is the nature of the concurrency? Are you opening multiple connections to the same database, or sharing a single connection between threads? Are you trying to write concurrently, and if so, is it to the same table or to different tables? Basically, there are three ways SQLite can be used (they can also be combined): 1. Many connections to the same database. In this case, there's a many-readers-single-writer lock at the database level, so at any point in time only one connection can write. 2. A single connection shared by multiple threads. A connection maintains a mutex that every API call acquires on entry and releases on return, so all calls are serialized. However, one thread can, say, step through a select resultset row-by-row, while another inserts row after row into some table: these calls can interleave, and would appear almost concurrent. Note that transactions are maintained on a per-connection basis. So if one thread starts a transaction and then another thread writes through the same connection, that change becomes part of that transaction. If the original thread then rolls it back (perhaps unaware that the other thread also made changes), all changes will be lost. 3. Multiple connections sharing cache: http://sqlite.org/sharedcache.html This mode supports many-readers-single-writer locks on a per-table basis, rather than on the whole database. Multiple connections can write simultaneously, as long as they write to different tables. Further, you can turn on a "read uncommitted" mode: this way, reads are not blocked at all, but they can read inconsistent data, or even data that will never actually exist in the database (if the transaction is later rolled back). It is rare, in my experience, that the database schema is amenable to being used in this mode. You need groups of tables that are largely unrelated to each other and so can be updated independently, at which point you might consider just splitting them into multiple databases and go with #1. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
