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

[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 goes directly to the database.  Unprivileged
> > users without write access to the RPM database are expected to run
> > read-only queries against the database.  Privileged users
> > (basically, root) is expected to use locking to exclude concurrent
> > writers.  But read-only users should not be able to stop
> > acquisition of a write lock.
> >
> > Is there a way to do this with SQLite?
> 
> The readers can open the database using URI filenames
> (https://www.sqlite.org/uri.html) with query parameters "mode=ro" and
> "locking=0".  That will prevent the readers from blocking the writer.
> But, if a write happens in the middle of a read, the reader might see
> inconsistent data and report SQLITE_CORRUPT.  This is harmless in the
> sense that the database file is not really corrupt (the reader is
> merely seeing parts of the files from two different points in time)
> and subsequent reads should still work.  If you are unlucky, a write
> that happens at the same time as a read might cause the reader to
> return incorrect results, so the reader can never be 100% sure that
> the answer it gets back is correct.

If writer safely forces a schema change, that's no real change to
schema, only an increase  to schema_version pragma; the readers can
check that condition at sqlite3_step.

I don't know how to fire it, and changing directly pragma value is
discouraged.


> 
> How important is it to you that the reader always get a correct
> answer?
> -- 
> D. Richard Hipp
> d...@sqlite.org

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
retroactively verifies that the entire read operation was consistent? The web
page currently says that “readers need write access to locks and lock file”:

  


Readers are lock-free/wait-free. Since LMDB uses MVCC readers get their own 
fully isolated snapshot of the DB so no retroactive verification is needed.


The restriction on opening the database twice within the same process is
something which we would have to work around, too.

I see you are working on eliminating the key size limit, which is nice.


Yeah, should be available in a month or so in 1.0 release candidates.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 24 November 2016 at 21:59, Florian Weimer  wrote:
> 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
> server,
> all access goes directly to the database.  Unprivileged users without
> write access to the RPM database are expected to run read-only queries
> against the database.  Privileged users (basically, root) is
> expected to
> use locking to exclude concurrent writers.  But read-only users should
> not be able to stop acquisition of a write lock.
>
> Is there a way to do this with SQLite?
>>
>>
>> Seems like quite a lot of burden to go this route. SQLite has a much
>> larger footprint than BDB, and much worse performance overall.
>
>
> SQLite has zero footprint for us because central parts of the system use it
> as well, and it is not likely to go away.  We also use the full Transaction
> Data Store, so the Berkeley DB shared object is about twice as large as the
> SQLite object.
>
>> 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
> retroactively verifies that the entire read operation was consistent? The
> web page currently says that “readers need write access to locks and lock
> file”:
>
>   
>
> The restriction on opening the database twice within the same process is
> something which we would have to work around, too.
>
> I see you are working on eliminating the key size limit, which is nice.
>
> Thanks,
> Florian
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
server,
all access goes directly to the database.  Unprivileged users without
write access to the RPM database are expected to run read-only queries
against the database.  Privileged users (basically, root) is
expected to
use locking to exclude concurrent writers.  But read-only users should
not be able to stop acquisition of a write lock.

Is there a way to do this with SQLite?


Seems like quite a lot of burden to go this route. SQLite has a much
larger footprint than BDB, and much worse performance overall.


SQLite has zero footprint for us because central parts of the system use 
it as well, and it is not likely to go away.  We also use the full 
Transaction Data Store, so the Berkeley DB shared object is about twice 
as large as the SQLite object.



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 
retroactively verifies that the entire read operation was consistent? 
The web page currently says that “readers need write access to locks and 
lock file”:


  

The restriction on opening the database twice within the same process is 
something which we would have to work around, too.


I see you are working on eliminating the key size limit, which is nice.

Thanks,
Florian

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 database.  Unprivileged users without
write access to the RPM database are expected to run read-only queries
against the database.  Privileged users (basically, root) is expected to
use locking to exclude concurrent writers.  But read-only users should
not be able to stop acquisition of a write lock.

Is there a way to do this with SQLite?


Seems like quite a lot of burden to go this route. SQLite has a much larger 
footprint than BDB, and much worse performance overall. 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. But it would be more 
efficient to just use LMDB directly, and not incur the overhead of the SQL 
translation layer.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 users without
write access to the RPM database are expected to run read-only queries
against the database.  Privileged users (basically, root) is expected to
use locking to exclude concurrent writers.  But read-only users should
not be able to stop acquisition of a write lock.

Is there a way to do this with SQLite?


The readers can open the database using URI filenames
(https://www.sqlite.org/uri.html) with query parameters "mode=ro" and
"locking=0".  That will prevent the readers from blocking the writer.


Thanks, this looks promising.

For the writers, can I use an external lock file (accessible only by 
root), and specify “locking=0” as well?  I need to avoid stalling on a 
read lock on the database file because any process which can open the 
file could create such a lock, thus blocking regular SQLite operation.



But, if a write happens in the middle of a read, the reader might see
inconsistent data and report SQLITE_CORRUPT.  This is harmless in the
sense that the database file is not really corrupt (the reader is
merely seeing parts of the files from two different points in time)
and subsequent reads should still work.  If you are unlucky, a write
that happens at the same time as a read might cause the reader to
return incorrect results, so the reader can never be 100% sure that
the answer it gets back is correct.


Could I use PRAGMA data_version to detect the case where there answer 
might be silently incorrect?



How important is it to you that the reader always get a correct answer?


An incorrect answer or SQLITE_CORRUPT is borderline acceptable.  Endless 
loops or crashes would be bad.


I need to run a test how often the existing code gets a wrong answer.  I 
can't imagine that it always gets this right because Berkeley DB does 
not really support this use case, either.  Either readers block writers, 
or there will be wrong data occasionally.


Thanks,
Florian
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 else write access on the directory 
> containing the database file if the "-shm" file does not exist.
> ”
> 
> So WAL mode does not work in this scenario.

Good point.  However, I believe it works fine, in that as long as Unprivileged 
users without write access don’t actually try to write to the database file the 
system works.  However, you may be correct and you should test it under your 
operating system.

One other thing to try would be to have a read/write client open the database 
before any read-only clients need to open it. That would create the .shm file 
meaning that the Unprivileged users don’t have to.  However, I can understand 
if this solution is not suitable for your situation.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 database are expected to run read-only queries
> against the database.  Privileged users (basically, root) is expected to
> use locking to exclude concurrent writers.  But read-only users should
> not be able to stop acquisition of a write lock.
>
> Is there a way to do this with SQLite?

The readers can open the database using URI filenames
(https://www.sqlite.org/uri.html) with query parameters "mode=ro" and
"locking=0".  That will prevent the readers from blocking the writer.
But, if a write happens in the middle of a read, the reader might see
inconsistent data and report SQLITE_CORRUPT.  This is harmless in the
sense that the database file is not really corrupt (the reader is
merely seeing parts of the files from two different points in time)
and subsequent reads should still work.  If you are unlucky, a write
that happens at the same time as a read might cause the reader to
return incorrect results, so the reader can never be 100% sure that
the answer it gets back is correct.

How important is it to you that the reader always get a correct answer?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 are expected to run read-only queries against the database. 
 Privileged users (basically, root) is expected to use locking to exclude 
concurrent writers.  But read-only users should not be able to stop acquisition 
of a write lock.

Is there a way to do this with SQLite?


From the above you would want to use WAL mode.  You can read about it here:




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 else write access 
on the directory containing the database file if the "-shm" file does 
not exist.

”

So WAL mode does not work in this scenario.

Florian
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 24 November 2016 at 12:10, 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 are expected to run read-only queries against the 
>> database.  Privileged users (basically, root) is expected to use locking to 
>> exclude concurrent writers.  But read-only users should not be able to stop 
>> acquisition of a write lock.
>>
>> Is there a way to do this with SQLite?
>
> From the above you would want to use WAL mode.  You can read about it here:
>
> 
>
> To put a database into WAL mode,
>
> 1) Open the database file.
> 2) If the database file is newly created (i.e. blank) Put at least one schema 
> element into the file (e.g. create a TABLE).
> 3) Issue the command "PRAGMA journal_mode=WAL" in any program.
> 4) Close the database file normally.
>
> You can use the SQLite command-line tool to do the above if you wish, rather 
> than having to write your own software to do it.  Once the database is set to 
> this mode, that information is saved in the database file.  All connections 
> which open it will automatically know it must be handled in WAL mode.
>
>> One way that would work is to copy the database file after each modification 
>> to a read-only public view.  But the database can be fairly large, so this 
>> doesn't look feasible until we have reflink support at the file system level.
>
> You should not have to worry about this level of things yourself.  The SQLite 
> library handles this problem for you.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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 against the 
> database.  Privileged users (basically, root) is expected to use locking to 
> exclude concurrent writers.  But read-only users should not be able to stop 
> acquisition of a write lock.
> 
> Is there a way to do this with SQLite?

From the above you would want to use WAL mode.  You can read about it here:



To put a database into WAL mode,

1) Open the database file.
2) If the database file is newly created (i.e. blank) Put at least one schema 
element into the file (e.g. create a TABLE).
3) Issue the command "PRAGMA journal_mode=WAL" in any program.  
4) Close the database file normally.

You can use the SQLite command-line tool to do the above if you wish, rather 
than having to write your own software to do it.  Once the database is set to 
this mode, that information is saved in the database file.  All connections 
which open it will automatically know it must be handled in WAL mode.

> One way that would work is to copy the database file after each modification 
> to a read-only public view.  But the database can be fairly large, so this 
> doesn't look feasible until we have reflink support at the file system level.

You should not have to worry about this level of things yourself.  The SQLite 
library handles this problem for you.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Florian Weimer]

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 database are expected to run read-only queries 
against the database.  Privileged users (basically, root) is expected to 
use locking to exclude concurrent writers.  But read-only users should 
not be able to stop acquisition of a write lock.


Is there a way to do this with SQLite?

One way that would work is to copy the database file after each 
modification to a read-only public view.  But the database can be fairly 
large, so this doesn't look feasible until we have reflink support at 
the file system level.


Thanks,
Florian
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users