On 10 Feb 2018, at 7:07am, Leonard Lausen <leon...@lausen.nl> wrote:
> thanks for your clarification.
You're welcome. A couple of things you wrote make me think I phrased things
poorly so I'm just taking this opportunity to illustrate what I wrote.
>> Will any of the processes accessing the database have write permission
>> ? If not, if they're all just reading the existing database, then
>> there's no opportunity for corruption. Think of it as
>
> All of the processes (on "main" and remote host) have read and write
> access, though I am only reading data in the processes accessing the db
> on a network filesystem. So it may be safer to open the database with
> SQLITE_OPEN_READONLY on the remote host.
SQLITE_OPEN_READONLY on the remote processes won't change anything important
given that the remote processes aren't making changes anyway. What's important
is that nothing is making changes to the database while it's being read. Even
if the remote processes are only reading, not writing, they can still get bad
data if a local process is making changes to the database at the same time.
>> 1) Reading a database while it's being written to may yield corrupt results.
>> SQLite uses locking to avoid this.
>> 2) Two processes writing a database at once will corrupt the database.
>> SQLite uses locking to avoid this.
>> 3) Locking does not work properly across a network
>
> Thanks for making me aware of potential corrupt results on read.
>
> Due to the potential corruption I changed my scripts to ssh into the
> main host and create a backup of the database with 'journal_mode delete'
> that can then safely be used to read data even when on a network
> filesystem.
The journal mode of the database doesn't matter. It can still provide
corrupted data if locking isn't working properly.
Here's an example of what can happen if locking isn't working properly. This
is contrived and probably fake (in real life things might not work like this),
but it demonstrates a problem in a simple way.
Table "people":
Anna, 28
Bertie, 40
Edna, 42
Fred, 88
Gillian, 90
An index on table "people" of (age)
Process 1 (accessing the file locally, read-write):
UPDATE people SET age="38" WHERE name="Fred"
Process 2 (accessing the file remotely, read-only):
SELECT name,age FROM people ORDER BY age
Suppose the two happen with timing such that the UPDATE is written to the file
when the SELECT is between "Bertie" and "Edna" The "Fred" row will not be
returned by the SELECT !
Now carry this through to how in the file pointers are used to point to rows,
and you can see similar problems even if you are just inserting a new row or
deleting an old one. Rather than getting incorrect data, you can get a result
indicating that the file is corrupt. But you might not, you might just get
incorrect data as in the above example.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
