Re: [sqlite] Concurrency support for multiple process
> I expect process B able to > read the old data and displays appropriately. But, process B seems to be > blocked. Why is this so as I thought SQLite handle concurrency as in this > case where there are multiple reads and one single write ? > After Pavel's explanation just reread http://www.sqlite.org/lockingv3.htmland can add that PENDING lock theoretically allow reading for an existing SHARED reader, but as I suppose single SELECT operation acquires SHARED and releases it right at the end of the query so if your "display" process is not in process of some sqlite operation, it probably has no SHARED lock so for any new Select it is considered "new" so prohibited. As long as I see the moment when the lock moves from RESERVED to PENDING depends on the write cache size. I just made a test with two admins accessing the same base and for a fresh base without any pragma tuning several megabytes of inserts passed without changing the lock. But the question is why do you need this kind of concurrency at all? If your insert is small and wrapped in a single transaction, your "display" application should not show noticeable "hanging" (if it just waits for SQLITE_BUSY to disappear). On the other side If you do some caching starting transaction and ending it only after some time, you may consider Commit on a time basis so to give your "display" process chance to read the base. Anyway I think holding single transaction for a long time is not a good idea so coniser optimizing your write operations Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency support for multiple process
It's not clear what locks did you want to show with your diagram but in reality locks would be held as following: *Process A*: Begin Transaction - no lock *Process B*: Select DB1 <-- shared lock *Process A*: Insert DB1 <-- if insert is small then only reserved lock *Process B*: Select DB1 <-- shared lock still can be obtained *Process A*: Insert DB1 <-- if insert is big then pending lock is obtained *Process B*: Select DB1 <-- cannot get shared lock, blocking *Process A*: <-- whatever it does here it still has pending lock *Process A*: Commit Transaction <-- exclusive lock then release all locks *Process B*: Can proceed with blocked Select and do anything else Pavel On Thu, Jan 28, 2010 at 7:00 AM, nyetngoh wong wrote: > Hi, > Is the locking states shown below for DB1 correct ? > > *Process A* > *Process B* > Begin Transaction > Insert DB1 <-- Reserved lock > Select DB1 <-- Shared lock > Insert DB1 > : > after 10 Inserts and 10 Selects > > Select DB1 <-- Shared lock > : > Continue Inserts and Selects > : > > End Transaction <-- Pending to Exclusive Lock > > > On Thu, Jan 28, 2010 at 7:42 PM, Max Vlasov wrote: > >> > >> > > SQLite allows multiple readers OR a single writer to access the >> database >> > simultaneously. >> > From the SQLite doc, as long as no transaction is pending, other process >> > can >> > read or obtain reserved lock for write. >> > >> >> the docs say: "Locks are not acquired until the first read or write >> operation. The first read operation against a database creates a SHARED >> lock >> and the first write operation creates a RESERVED lock.". >> >> So your only chance to have concurrent access from both processes only if >> the first one starts transaction and does something innocent like >> preparations with selects, temp tables and so on. Any UPDATE or INSERT will >> lead to RESERVED lock and process B suspension with db access. >> >> Max >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency support for multiple process
Hi, Is the locking states shown below for DB1 correct ? *Process A* *Process B* Begin Transaction Insert DB1<-- Reserved lock Select DB1<-- Shared lock Insert DB1 : after 10 Inserts and 10 Selects Select DB1<-- Shared lock : Continue Inserts and Selects : End Transaction <-- Pending to Exclusive Lock On Thu, Jan 28, 2010 at 7:42 PM, Max Vlasov wrote: > > > > > SQLite allows multiple readers OR a single writer to access the > database > > simultaneously. > > From the SQLite doc, as long as no transaction is pending, other process > > can > > read or obtain reserved lock for write. > > > > the docs say: "Locks are not acquired until the first read or write > operation. The first read operation against a database creates a SHARED > lock > and the first write operation creates a RESERVED lock.". > > So your only chance to have concurrent access from both processes only if > the first one starts transaction and does something innocent like > preparations with selects, temp tables and so on. Any UPDATE or INSERT will > lead to RESERVED lock and process B suspension with db access. > > Max > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency support for multiple process
> > > SQLite allows multiple readers OR a single writer to access the database > simultaneously. > From the SQLite doc, as long as no transaction is pending, other process > can > read or obtain reserved lock for write. > the docs say: "Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock.". So your only chance to have concurrent access from both processes only if the first one starts transaction and does something innocent like preparations with selects, temp tables and so on. Any UPDATE or INSERT will lead to RESERVED lock and process B suspension with db access. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency support for multiple process
> What made you expect that? Process A has not entered the exclusive lock and so process B can obtain shared lock to read. > SQLite allows multiple readers OR a single writer to access the database simultaneously. >From the SQLite doc, as long as no transaction is pending, other process can read or obtain reserved lock for write. On Thu, Jan 28, 2010 at 12:51 PM, Igor Tandetnik wrote: > nyetngoh wong wrote: > > First, I've a process A that do many inserts to the database and reads > back > > from the database to verify. The writes are done in one DEFERRED > transaction > > as data are not committed yet. While the first process running, another > > process B is launched to read from the database for display purposes. > > Process B will halts until the transaction ends. I expect process B able > to > > read the old data and displays appropriately. > > What made you expect that? > > > But, process B seems to be > > blocked. Why is this so as I thought SQLite handle concurrency as in this > > case where there are multiple reads and one single write ? > > SQLite allows multiple readers OR a single writer to access the database > simultaneously. > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency support for multiple process
nyetngoh wong wrote: > First, I've a process A that do many inserts to the database and reads back > from the database to verify. The writes are done in one DEFERRED transaction > as data are not committed yet. While the first process running, another > process B is launched to read from the database for display purposes. > Process B will halts until the transaction ends. I expect process B able to > read the old data and displays appropriately. What made you expect that? > But, process B seems to be > blocked. Why is this so as I thought SQLite handle concurrency as in this > case where there are multiple reads and one single write ? SQLite allows multiple readers OR a single writer to access the database simultaneously. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concurrency support for multiple process
Hi, First, I've a process A that do many inserts to the database and reads back from the database to verify. The writes are done in one DEFERRED transaction as data are not committed yet. While the first process running, another process B is launched to read from the database for display purposes. Process B will halts until the transaction ends. I expect process B able to read the old data and displays appropriately. But, process B seems to be blocked. Why is this so as I thought SQLite handle concurrency as in this case where there are multiple reads and one single write ? Please help. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
