Re: [sqlite] Connection philosophy

2013-01-23 Thread Ward Willats
On Jan 22, 2013, at 8:18 PM, Keith Medcalf wrote: > I presume that you are using some kind of input-driven or event driven > application which may get a request to process a query "in the middle" of > your update transaction. That is correct. > One of the advantages of WAL and using a sepa

Re: [sqlite] Connection philosophy

2013-01-22 Thread Keith Medcalf
On Tuesday, 22 January, 2013 19:14 MST, Ward Willats wrote: > On Jan 22, 2013, at 5:54 PM, Keith Medcalf wrote: >> In my case, I only have one writer (I think!) during the big transaction, >> so a long-lived, singleton connection or WAL should work for me. I guess I >> would lean toward the

Re: [sqlite] Connection philosophy

2013-01-22 Thread Keith Medcalf
On Tuesday, 22 January, 2013 19:14 MST, Ward Willats wrote: > On Jan 22, 2013, at 5:54 PM, Keith Medcalf wrote: >> I prefer the long-lived approach. Continuously re-initialization of the >> connection on open, the need to re-read pages into the page cache >> repetitively, and the subsequent

Re: [sqlite] Connection philosophy

2013-01-22 Thread Ward Willats
On Jan 22, 2013, at 5:54 PM, Keith Medcalf wrote: > I prefer the long-lived approach. Continuously re-initialization of the > connection on open, the need to re-read pages into the page cache > repetitively, and the subsequent discard of a nicely loaded page-cache on > connection close usual

Re: [sqlite] Connection philosophy

2013-01-22 Thread Keith Medcalf
> All is well, EXCEPT, I have ONE big, long transaction that collects and > stores a lot of data from some sensors. If this data is big enough, it > will eventually cause that connection to obtain an exclusive lock on the > DB. Now if the data collection code subsequently calls any subroutine that

Re: [sqlite] Connection philosophy

2013-01-22 Thread Ward Willats
On Jan 22, 2013, at 10:07 AM, Simon Slavin wrote: > Change the code used in your one big thread so that it counts the number if > INSERT/UPDATEs it does and changes transactions and does a little pause after > every thousand ops. Or hundred. Whatever. > Cool idea, except the folks in Marke

Re: [sqlite] Connection philosophy

2013-01-22 Thread Simon Slavin
On 22 Jan 2013, at 5:39pm, Ward Willats wrote: > I have a bunch of home-grown C++ wrappers for Sqlite and when my app needs to > use the DB, most routines just instance one of these DB wrapper objects on > the stack and go for it. The constructor creates a new DB connection, which > is closed