Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Keith Medcalf
vember, 2016 11:39 > To: SQLite mailing list > Subject: Re: [sqlite] Read-only access which does not block writers > > One other question about WAL mode and simultaneous readers and writers: > How are people setting their page caches? My read is that shared cache is > probably

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread James K. Lowden
On Thu, 24 Nov 2016 22:59:32 +0100 Florian Weimer wrote: > Does LMDB perform lock-free optimistic reads and > retroactively verifies that the entire read operation was consistent? In LMDB there are readers and reader-writers. A reader never writes; a reader-writer may

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Simon Slavin
On 29 Nov 2016, at 6:10pm, Mark Hamburg wrote: > In the single WAL scenario, what I probably really want is a way for the > checkpoint operation on the write connection to do its work then wait for > exclusive access — standard reader/writer lock pattern — to do the WAL

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Jens Alfke
> On Nov 29, 2016, at 9:09 AM, Simon Slavin wrote: > > You cannot design a system which (A) provides up-to-date data to readers (B) > allows writers to get rid of their data immediately without ever locking up > and (C) guarantees that earlier changes to the data are

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Scott Hess
On Tue, Nov 29, 2016 at 10:10 AM, Mark Hamburg wrote: > On Nov 29, 2016, at 9:09 AM, Simon Slavin wrote: >>> On 29 Nov 2016, at 4:18pm, Mark Hamburg wrote: >>> >>> Does this make sense? Does it seem useful? (It seems useful to

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Mark Hamburg
On Nov 29, 2016, at 9:09 AM, Simon Slavin wrote: > > >> On 29 Nov 2016, at 4:18pm, Mark Hamburg wrote: >> >> Does this make sense? Does it seem useful? (It seems useful to me when I see >> multi-megabyte WAL files.) > > Sorry, but I cannot spare

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Simon Slavin
On 29 Nov 2016, at 4:18pm, Mark Hamburg wrote: > Does this make sense? Does it seem useful? (It seems useful to me when I see > multi-megabyte WAL files.) Sorry, but I cannot spare the time right now to analyze the system you laid out. It usually takes half an hour to

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Mark Hamburg
One other question about WAL mode and simultaneous readers and writers: How are people setting their page caches? My read is that shared cache is probably not what's wanted. I was setting my reader caches to be bigger than my writer cache under the assumption that writers write and then move on

Re: [sqlite] Read-only access which does not block writers

2016-11-29 Thread Mark Hamburg
Once I figured out how to reliably get the reader and writer connections open on a database — key point, when creating the database let the writer get through all of the creation work before opening any readers — I've been getting great concurrency behavior for simultaneous reads and writes in

Re: [sqlite] Read-only access which does not block writers

2016-11-28 Thread Nico Williams
On Mon, Nov 28, 2016 at 07:45:16PM +, Howard Chu wrote: > Nico Williams wrote: > >One of the problems with LMDB's locks is that readers need write > >permission to the lock file, IIRC :( but at least it's a separate file. > > We developed an alternate locking protocol for Postfix. It just

Re: [sqlite] Read-only access which does not block writers

2016-11-28 Thread Howard Chu
Nico Williams wrote: On Fri, Nov 25, 2016 at 09:08:37AM +0100, Florian Weimer wrote: I think you misunderstood what I was asking (see the start of the thread). I need to support Byzantine readers which do not follow the locking protocol. Based on the documentation, LMDB uses locks to implement

Re: [sqlite] Read-only access which does not block writers

2016-11-28 Thread Nico Williams
On Fri, Nov 25, 2016 at 09:08:37AM +0100, Florian Weimer wrote: > I think you misunderstood what I was asking (see the start of the thread). > I need to support Byzantine readers which do not follow the locking > protocol. Based on the documentation, LMDB uses locks to implement MVCC and >

Re: [sqlite] Read-only access which does not block writers

2016-11-25 Thread Richard Hipp
On 11/24/16, Paul Sanderson wrote: > Could you use > > PRAGMA data_version > > before and after each read to see whether there have been any changes > to the DB - not surehow this works in WAL mode? I think that would work as long as the writer never does ROLLBACK.

Re: [sqlite] Read-only access which does not block writers

2016-11-25 Thread Richard Hipp
On 11/24/16, Florian Weimer wrote: > > An incorrect answer or SQLITE_CORRUPT is borderline acceptable. Endless > loops or crashes would be bad. > We do a lot of testing to ensure that SQLite does not crash or get into endless loops or leak memory when confronted with a

Re: [sqlite] Read-only access which does not block writers

2016-11-25 Thread Florian Weimer
On 11/25/2016 02:07 AM, Howard Chu wrote: Florian Weimer wrote: On 11/24/2016 10:41 PM, Howard Chu wrote: As a compromise you could use SQLightning, which replaces SQLite's Btree layer with LMDB. Since LMDB *does* allow readers that don't block writers. How does it do that? Does LMDB

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Eduardo Morras
On Thu, 24 Nov 2016 08:54:47 -0500 Richard Hipp wrote: > On 11/24/16, Florian Weimer wrote: > > I'd like to replace the use of Berkeley DB in RPM with SQLite. > > > > The scenario is special in the follow way. There is no database > > server, all access

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Howard Chu
Florian Weimer wrote: On 11/24/2016 10:41 PM, Howard Chu wrote: As a compromise you could use SQLightning, which replaces SQLite's Btree layer with LMDB. Since LMDB *does* allow readers that don't block writers. How does it do that? Does LMDB perform lock-free optimistic reads and

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Paul Sanderson
Could you use PRAGMA data_version before and after each read to see whether there have been any changes to the DB - not surehow this works in WAL mode? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Florian Weimer
On 11/24/2016 10:41 PM, Howard Chu wrote: Florian Weimer wrote: On 11/24/2016 02:54 PM, Richard Hipp wrote: On 11/24/16, Florian Weimer wrote: I'd like to replace the use of Berkeley DB in RPM with SQLite. The scenario is special in the follow way. There is no database

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Howard Chu
Florian Weimer wrote: On 11/24/2016 02:54 PM, Richard Hipp wrote: On 11/24/16, Florian Weimer wrote: I'd like to replace the use of Berkeley DB in RPM with SQLite. The scenario is special in the follow way. There is no database server, all access goes directly to the

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Florian Weimer
On 11/24/2016 02:54 PM, Richard Hipp wrote: On 11/24/16, Florian Weimer wrote: I'd like to replace the use of Berkeley DB in RPM with SQLite. The scenario is special in the follow way. There is no database server, all access goes directly to the database. Unprivileged

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Simon Slavin
On 24 Nov 2016, at 1:41pm, Florian Weimer wrote: > Item 5 says: > > “ > It is not possible to open read-only WAL databases. The opening process must > have write privileges for "-shm" wal-index shared memory file associated with > the database, if that file exists, or

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Richard Hipp
On 11/24/16, Florian Weimer wrote: > I'd like to replace the use of Berkeley DB in RPM with SQLite. > > The scenario is special in the follow way. There is no database server, > all access goes directly to the database. Unprivileged users without > write access to the RPM

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Florian Weimer
On 11/24/2016 01:10 PM, Simon Slavin wrote: On 24 Nov 2016, at 11:02am, Florian Weimer wrote: The scenario is special in the follow way. There is no database server, all access goes directly to the database. Unprivileged users without write access to the RPM database

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Paul Sanderson
Steps 2 and 3 can be swapped Also you can convert an existing database from one mode to another (although not, I suspect (I have not tried), in the middle of a transaction and if in the DB is wal mode and you are changing to journal then this would force a checkpoint). Paul

Re: [sqlite] Read-only access which does not block writers

2016-11-24 Thread Simon Slavin
On 24 Nov 2016, at 11:02am, Florian Weimer wrote: > The scenario is special in the follow way. There is no database server, all > access goes directly to the database. Unprivileged users without write > access to the RPM database are expected to run read-only queries