[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
On 9/4/15, Todd Biggins wrote: > Thanks Richard! > > So if we remove the remote machine and focus on the same host - running as > a separate process based on System.Data.SQLite > can I safely run a SELECT query without harming the database integrity or > the 3rd party app writing to the database? That's all you need to do. There is one corner case: If the main application is writing extensively and the secondary reader app does a long-running read transaction, the read transaction can prevent the main application from resetting the WAL file. This can cause the WAL file to grow very large. Everything should still work - it will just use more disk space. The WAL will reset automatically once the read transaction completes. -- D. Richard Hipp drh at sqlite.org
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
Thanks Richard! So if we remove the remote machine and focus on the same host - running as a separate process based on System.Data.SQLite can I safely run a SELECT query without harming the database integrity or the 3rd party app writing to the database? If so, is it a simple matter of executing the select from the same host, or do I need to set readonly on the System.Data.SQLite data connection or ? Again - my goal is not to affect the existing database's internal settings. I truly appreciate your input! On Fri, Sep 4, 2015 at 11:24 AM, Richard Hipp wrote: > WAL mode requires the use of shared memory (in this case implemented > using the mmapped -shm file) in order to coordinate access to the > write-ahead log by various processes doing access. But in order to > share memory, all processes accessing the database have to be on the > same machine, obviously. > > So, no, it is not possible to run a database in WAL mode on one > machine and simultaneously access that database over a network share > from another machine. You'll have to switch to one of the rollback > modes (ex: DELETE) in order to do that. > > On 9/4/15, Todd Biggins wrote: > > Hello all, > > > > I've been digging through SQLite.org and these forums, but I was hoping > for > > a final consensus on my conundrum: > > > > I have a third party app that is writing to a local v3.0 SQLite database > > with WAL. I want to access it outside of the existing app solely for the > > purpose of SELECT queries (using System.Data.SQLite) - these queries > would > > be run by only one other process, either from the same host or a remote > > machine (by sharing the database folder through NTFS/SMB) > > > > Am I able to run SELECT queries without altering the database integrity > for > > the other app? Since the database is using WAL, it looks like I need > write > > access to the folder structure, and even a SELECT query attempts to > > generate the -WAL and -SHM file on the database - files which already > exist > > from the 3rd party app. > > > > To reiterate - is there a "safe" way to use System.Data.SQLite to > passively > > view tables without altering the database or its cache? > > > > Thanks so much for your time! > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
WAL mode requires the use of shared memory (in this case implemented using the mmapped -shm file) in order to coordinate access to the write-ahead log by various processes doing access. But in order to share memory, all processes accessing the database have to be on the same machine, obviously. So, no, it is not possible to run a database in WAL mode on one machine and simultaneously access that database over a network share from another machine. You'll have to switch to one of the rollback modes (ex: DELETE) in order to do that. On 9/4/15, Todd Biggins wrote: > Hello all, > > I've been digging through SQLite.org and these forums, but I was hoping for > a final consensus on my conundrum: > > I have a third party app that is writing to a local v3.0 SQLite database > with WAL. I want to access it outside of the existing app solely for the > purpose of SELECT queries (using System.Data.SQLite) - these queries would > be run by only one other process, either from the same host or a remote > machine (by sharing the database folder through NTFS/SMB) > > Am I able to run SELECT queries without altering the database integrity for > the other app? Since the database is using WAL, it looks like I need write > access to the folder structure, and even a SELECT query attempts to > generate the -WAL and -SHM file on the database - files which already exist > from the 3rd party app. > > To reiterate - is there a "safe" way to use System.Data.SQLite to passively > view tables without altering the database or its cache? > > Thanks so much for your time! > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
Hello all, I've been digging through SQLite.org and these forums, but I was hoping for a final consensus on my conundrum: I have a third party app that is writing to a local v3.0 SQLite database with WAL. I want to access it outside of the existing app solely for the purpose of SELECT queries (using System.Data.SQLite) - these queries would be run by only one other process, either from the same host or a remote machine (by sharing the database folder through NTFS/SMB) Am I able to run SELECT queries without altering the database integrity for the other app? Since the database is using WAL, it looks like I need write access to the folder structure, and even a SELECT query attempts to generate the -WAL and -SHM file on the database - files which already exist from the 3rd party app. To reiterate - is there a "safe" way to use System.Data.SQLite to passively view tables without altering the database or its cache? Thanks so much for your time!
