Re: [sqlite] concurrency differences between in-memory and on disk?
pierr wrote: > So, for "A single connection shared by multiple threads" case, is the > statement "there's a > many-readers-single-writer lock at the database level" still ture? Well, the lock is still there, but since there are no other connections, there's noone to be locked out by it. The connection itself is not affected by its own lock. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concurrency differences between in-memory and on disk?
Igor Tandetnik wrote: > > 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. > Hi Igor, So, for "A single connection shared by multiple threads" case, is the statement "there's a many-readers-single-writer lock at the database level" still ture? At least , the read and write would appear concurrent as you said. Is there any examples showing the difference between these 2 different usage model? Thanks. - Pierr -- View this message in context: http://www.nabble.com/concurrency-differences-between-in-memory-and-on-disk--tp24201096p24266195.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] concurrency differences between in-memory and on disk?
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
