Re: [sqlite] When is data committed on one connection seen on another?

On Wed, 14 Sep 2016 18:29:36 + (UTC) Alex Ward wrote: > Perhaps our schema needs a rework, would one table with a million > rows be better than 500 tables with 2000 rows each? 500 tables isn't right or wrong, but *counting* tables is. Table count is not a design-quality

Re: [sqlite] When is data committed on one connection seen on another?

On 2016/09/14 8:29 PM, Alex Ward wrote: We currently have 500 tables and 1500 triggers in the schema. Perhaps that is why we didn't have much luck having one connection per thread or opening a connection per access. Perhaps our schema needs a rework, would one table with a million rows

Re: [sqlite] When is data committed on one connection seen on another?

>> As already stated, and per my own experience, each thread should have it's >> own connection and do whatever it needs to do there, without interference >> from other threads. > >I appreciate this point. Early prototyping indicated that this might not be >possible for our system, which makes

Re: [sqlite] When is data committed on one connection seen on another?

Alex, > Le 14 sept. 2016 à 20:29, Alex Ward a écrit : > >> Unless you have a very complex schema (I would say at the very least more >> than on hundred tables and other create statement), opening a SQLite >> connection is lightweight, > > We currently have 500 tables and

Re: [sqlite] When is data committed on one connection seen on another?

On 14 Sep 2016, at 7:29pm, Alex Ward wrote: > Perhaps our schema needs a rework, would one table with a million rows be > better than 500 tables with 2000 rows each? Yes. As a guideline, if two tables have the same columns (or even nearly the same columns) you should

Re: [sqlite] When is data committed on one connection seen on another?

> On 14 Sep 2016, at 5:47pm, Alex Ward wrote: > >> Behalf Of Simon Slavin > >> It is not enough to lock the _prepare, lock the _step()s, and lock the >> _finalize. >> If they're sharing a connection with other threads then the lock has to be >> placed at the start of the

Re: [sqlite] When is data committed on one connection seen on another?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Olivier Mascia > If I'm permitted: you're wrong. Ha, yes, well if our initial threading model is anything to go by, I think you're on safe ground with that assertion. > Unless you have a very complex

Re: [sqlite] When is data committed on one connection seen on another?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Igor Tandetnik > Statement execution starts with the first sqlite3_step after sqlite3_prepare > or the most recent sqlite3_reset; and ends with sqlite3_reset or > sqlite3_finalize. Thanks makes sense.

Re: [sqlite] When is data committed on one connection seen on another?

> Le 14 sept. 2016 à 18:14, Alex Ward a écrit : > > I don't think we can afford to have a connection per thread. We have an > arbitrarily large number of threads coming and going all the time and a > combination of the amount of memory each connection is taking up and how

Re: [sqlite] When is data committed on one connection seen on another?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > It is not enough to lock the _prepare, lock the _step()s, and lock the > _finalize. > If they're sharing a connection with other threads then the lock has to be > placed at the start of

Re: [sqlite] When is data committed on one connection seen on another?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Hick Gunter > Sharing a connection between threads makes it practically impossible for any > one thread to tell when a transaction begins or ends. From the point of view > of the database connection, the

Re: [sqlite] When is data committed on one connection seen on another?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Your description of your fault suggests that at least two of your > threads/processes are trying to use the same connection to the database at > the same time We have a bunch of reads

Re: [sqlite] When is data committed on one connection seen on another?

AFAIK there is no "write mark" in the WAL journal. Instead, each read transaction (either implicit or explicit) has an associated "read mark" that determines which data the transaction will see (i.e. the data from the main file plus any pages in the WAL journal file before the read mark). This

Re: [sqlite] When is data committed on one connection seen on another?

On 13 Sep 2016, at 9:00pm, Alex Ward wrote: > What is the standard idiom to avoid stale data while still allowing all but a > writing thread not to see uncommitted data? You should not need to do anything special to arrange this. > Is there a window of time between a commit

Re: [sqlite] When is data committed on one connection seen on another?

On 9/13/2016 4:00 PM, Alex Ward wrote: We think we are seeing the case where a read on one WAL mode Sqlite database connection using the C API is reading stale data after a transaction has committed data changes on another connection. For instance, a deleted row on one connection is still

[sqlite] When is data committed on one connection seen on another?

We think we are seeing the case where a read on one WAL mode Sqlite database connection using the C API is reading stale data after a transaction has committed data changes on another connection. For instance, a deleted row on one connection is still found by a select on the other. The