Re: [sqlite] Data visibility problem

2014-06-26 Thread João Ramos
OK, thank you all for your support. On Thu, Jun 26, 2014 at 3:22 AM, Igor Tandetnik wrote: > On 6/25/2014 8:48 PM, João Ramos wrote: > >> Now that you mentioned the WAL, shouldn't this actually help prevent this >> scenario? >> > > Quite the opposite - WAL helps enable this scenario. With tradi

Re: [sqlite] Data visibility problem

2014-06-25 Thread Igor Tandetnik
On 6/25/2014 8:48 PM, João Ramos wrote: Now that you mentioned the WAL, shouldn't this actually help prevent this scenario? Quite the opposite - WAL helps enable this scenario. With traditional rollback journal, the writer would be unable to write at all while a reader is active, so issues of

Re: [sqlite] Data visibility problem

2014-06-25 Thread Keith Medcalf
>> "Externally, from the point of view of another process or thread, two >> or more database connections using a shared-cache appear as a single >> connection. The locking protocol used to arbitrate between multiple >> shared-caches or regular database users is described elsewhere." >Of course an

Re: [sqlite] Data visibility problem

2014-06-25 Thread João Ramos
On Wed, Jun 25, 2014 at 8:04 PM, Sohail Somani wrote: > On 22/06/2014 6:33 PM, João Ramos wrote: > >> The issue appeared with the following scenario: - using SQLite v3.8.4.3 - >> the DB is in WAL mode and running with multi-thread mode - every thread >> has >> a read-only DB connection (using thr

Re: [sqlite] Data visibility problem

2014-06-25 Thread João Ramos
On Wed, Jun 25, 2014 at 2:19 PM, Igor Tandetnik wrote: > On 6/25/2014 5:19 AM, João Ramos wrote: > >> And I understand the limitations a shared-cache mode may have, but if this >> is to be the case, it should be documented. I can be wrong, but I didn't >> find anywhere in the docs where this scen

Re: [sqlite] Data visibility problem

2014-06-25 Thread Sohail Somani
On 22/06/2014 6:33 PM, João Ramos wrote: The issue appeared with the following scenario: - using SQLite v3.8.4.3 - the DB is in WAL mode and running with multi-thread mode - every thread has a read-only DB connection (using thread-local-storage) - a single DB connection is shared between threads

Re: [sqlite] Data visibility problem

2014-06-25 Thread Igor Tandetnik
On 6/25/2014 5:19 AM, João Ramos wrote: And I understand the limitations a shared-cache mode may have, but if this is to be the case, it should be documented. I can be wrong, but I didn't find anywhere in the docs where this scenario is presented (or has some sort of warning). Well, one can't d

Re: [sqlite] Data visibility problem

2014-06-25 Thread João Ramos
On Mon, Jun 23, 2014 at 3:31 PM, Simon Slavin wrote: > > On 23 Jun 2014, at 11:17am, João Ramos wrote: > > > On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin > wrote: > > > >> Are you finalizing all these SQL commands (or using _exec() which > amounts > >> to the same thing) ? > > > > Yes, I doub

Re: [sqlite] Data visibility problem

2014-06-25 Thread João Ramos
On Mon, Jun 23, 2014 at 3:03 PM, Igor Tandetnik wrote: > On 6/23/2014 5:43 AM, João Ramos wrote: > >> If multiple connections in shared cache mode are treated as a single >> "real" >> connection, should I change the thread mode to Serialized? >> > > What mode do you use now? > > In any case, I do

Re: [sqlite] Data visibility problem

2014-06-23 Thread Simon Slavin
On 23 Jun 2014, at 11:17am, João Ramos wrote: > On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin wrote: > >> Are you finalizing all these SQL commands (or using _exec() which amounts >> to the same thing) ? > > Yes, I double checked and everything is being terminated properly (e.g.: > sqlite3_r

Re: [sqlite] Data visibility problem

2014-06-23 Thread Igor Tandetnik
On 6/23/2014 5:43 AM, João Ramos wrote: If multiple connections in shared cache mode are treated as a single "real" connection, should I change the thread mode to Serialized? What mode do you use now? In any case, I doubt the thread mode has any bearing on the problem. The scenario may occur

Re: [sqlite] Data visibility problem

2014-06-23 Thread João Ramos
On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin wrote: > > On 22 Jun 2014, at 11:33pm, João Ramos wrote: > > > What happens is this: - a thread acquires the write DB connection - that > > thread inserts a new row in a table and gets its id - > > Are you finalizing all these SQL commands (or using

Re: [sqlite] Data visibility problem

2014-06-23 Thread Simon Slavin
On 22 Jun 2014, at 11:33pm, João Ramos wrote: > What happens is this: - a thread acquires the write DB connection - that > thread inserts a new row in a table and gets its id - Are you finalizing all these SQL commands (or using _exec() which amounts to the same thing) ? > the thread releases

Re: [sqlite] Data visibility problem

2014-06-23 Thread João Ramos
If multiple connections in shared cache mode are treated as a single "real" connection, should I change the thread mode to Serialized? I tough this "single" connection was in terms of page cache and would not have any implications on how the connections are accessed (or not) concurrently, especiall

Re: [sqlite] Data visibility problem

2014-06-22 Thread Igor Tandetnik
On 6/22/2014 6:33 PM, João Ramos wrote: What happens is this: - a thread acquires the write DB connection - that thread inserts a new row in a table and gets its id - the thread releases the write DB connection (so that other threads can use it) - the same thread acquires (its own) read-only DB c

[sqlite] Data visibility problem

2014-06-22 Thread João Ramos
Hi, The issue appeared with the following scenario: - using SQLite v3.8.4.3 - the DB is in WAL mode and running with multi-thread mode - every thread has a read-only DB connection (using thread-local-storage) - a single DB connection is shared between threads to write - this write DB connection