[sqlite] Multiple instances of the same program accessing the same db file

[Keith Medcalf]

> > There is no difference between a file opened over the network and one
> opened locally, except that in the network open case the network
> filesystem does not maintain consistency between the client view of the
> file and the server view of that same file, because doing so takes time
> and the network filesystem designer decided to trade off in favour of
> speed over consistency, usually without provision of a knob to change this
> decision.

> Keith, I think you're ignoring the fact that when opening a file across a
> network the file system knows it's operating across the network and does
> not make the assumption that its cache is valid.  If things operated the
> way you describe almost all shared access operations would lead to
> corruption.  Operations when opening a file across a network are slower
> because the NFS has to check whether the file has been updated since its
> local copy was cached.

> The various teams which develop network file systems like NFS SMB and AFP
> all worried about these things and got them right.  Individual clients
> won't write out-of-date caches back to a centrally held file unless you've
> explicitly turned off the network-savvy routines.

I believe you are incorrect, Simon.  

To my knowledge there are *no* network filesystems which properly arbitrate 
multiple shared-read and shared-write client access to files opened across the 
network and provide a consistent view (similar to the view of the file that one 
would obtain if all the clients were operating on a local file with no network 
filesystem involved).  Moreover, as more and more time progresses there is less 
and less ability for things to work properly.  Except in very rare cases it has 
always been my experience that concurrent updates (as in record updates) leads 
to file corruption.

If shared network access worked properly then Client/Server computing would 
never have needed to be invented.

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 5 May 2015, at 1:57pm, Keith Medcalf  wrote:

> There is no difference between a file opened over the network and one opened 
> locally, except that in the network open case the network filesystem does not 
> maintain consistency between the client view of the file and the server view 
> of that same file, because doing so takes time and the network filesystem 
> designer decided to trade off in favour of speed over consistency, usually 
> without provision of a knob to change this decision.

Keith, I think you're ignoring the fact that when opening a file across a 
network the file system knows it's operating across the network and does not 
make the assumption that its cache is valid.  If things operated the way you 
describe almost all shared access operations would lead to corruption.  
Operations when opening a file across a network are slower because the NFS has 
to check whether the file has been updated since its local copy was cached.

The various teams which develop network file systems like NFS SMB and AFP all 
worried about these things and got them right.  Individual clients won't write 
out-of-date caches back to a centrally held file unless you've explicitly 
turned off the network-savvy routines.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 5 May 2015, at 11:44am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> In contrast to NFS both SMB and AFP are designed to support networks
>> properly, with caches only on the computer which hosts the file and
>> locking correctly implemented.  
> 
> Are you saying that SMB clients have no filebuffer cache?  Or that
> it exists, but is reliably invalidated by writes from other clients?

SMB keeps its cache on the computer which hosts the file.  So if a file is 
opened locally the cache is on the only computer concerned.  If computer A 
opens a file on computer B, the file-system cache is on computer B, where all 
file requests pass through it.

Of course a badly written program could keep a local cache too.  But that's the 
programmer's fault and SQLite certainly doesn't have its own cache at that 
level.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Keith Medcalf]

> SMB keeps its cache on the computer which hosts the file.  So if a file is
> opened locally the cache is on the only computer concerned.  If computer A
> opens a file on computer B, the file-system cache is on computer B, where
> all file requests pass through it.
> 
> Of course a badly written program could keep a local cache too.  But
> that's the programmer's fault and SQLite certainly doesn't have its own
> cache at that level.

The SQLite page cache is local to the application running SQLite, obviously.  
It cannot be located on the remote filesystem server since the remote server is 
not running SQLite.  Of course, what you *really* mean is that client's view of 
the dataset must be consistent with the servers' view for the dataset and that 
the mechanisms to maintain that consistency must work across the network, just 
as they work for a local file on a local filesystem.

There is no difference between a file opened over the network and one opened 
locally, except that in the network open case the network filesystem does not 
maintain consistency between the client view of the file and the server view of 
that same file, because doing so takes time and the network filesystem designer 
decided to trade off in favour of speed over consistency, usually without 
provision of a knob to change this decision.

[sqlite] Multiple instances of the same program accessing the same db file

[James K. Lowden]

On Mon, 4 May 2015 02:05:54 +0100
Simon Slavin  wrote:

> On 4 May 2015, at 1:30am, James K. Lowden 
> wrote:
> 
> > That is the way most remote filesystems are designed and implemented
> > and documented.  Cf. http://www.ietf.org/rfc/rfc1813.txt:
> > 
> >   4.11 Caching policies
> > 
> >   The NFS version 3 protocol does not define a policy for
> >   caching on the client or server. In particular, there is no
> >   support for strict cache consistency between a client and
> >   server, nor between different clients. See [Kazar] for a
> >   discussion of the issues of cache synchronization and
> >   mechanisms in several distributed file systems.
> > 
> > If you can find documentation for cache semantics for CIFS, I'd be
> > interested to see it.  
> 
> In contrast to NFS both SMB and AFP are designed to support networks
> properly, with caches only on the computer which hosts the file and
> locking correctly implemented.  

Are you saying that SMB clients have no filebuffer cache?  Or that
it exists, but is reliably invalidated by writes from other clients?  

>From what I remember from configuring Samba, SMB is 1) very
complicated (lots of versions and knobs) and 2) undocumented in
numerous ways. The prototocol is better documented these days, thanks
to the EU, but I've never seen anything resembling semantic
guarantees.  So I'm skeptical of any assertion that it Just Works, even
subject to the constraints you mention.  

--jkl

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 4 May 2015, at 8:51pm, Scott Doctor  wrote:

> The issue of locking a file during concurrent access seems to be a major 
> issue, mostly due to the schizophrenic abilities of network file systems. Has 
> the SQLite development team considered embedding its own file locking system 
> logic, such as creating its own lock file to prevent damage from concurrent 
> write access, that does not rely on the network file system?

Read this:



But it's worth pointing out that all these operations depend on the underlying 
network file system working correctly.  It's not possible for SQLite to do 
locking properly if the operating system or storage medium is lying to it.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Richard Hipp]

On 5/4/15, Scott Doctor  wrote:
>
> The issue of locking a file during concurrent access seems to be
> a major issue, mostly due to the schizophrenic abilities of
> network file systems. Has the SQLite development team considered
> embedding its own file locking system logic, such as creating
> its own lock file to prevent damage from concurrent write
> access, that does not rely on the network file system? Seems
> that if the issue is due to the design of the network file
> system, which we have no control, then embedding some internal
> logic system that handles self-flagging of a lock condition
> seems a reasonable course to pursue.
>

The "unix-dotfile" VFS (which is built into standard SQLite builds on
Unix) does exactly that.

Problem is that if your application crashes, it might leave behind
stale locks that need to be cleaned up manually.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

The issue of locking a file during concurrent access seems to be 
a major issue, mostly due to the schizophrenic abilities of 
network file systems. Has the SQLite development team considered 
embedding its own file locking system logic, such as creating 
its own lock file to prevent damage from concurrent write 
access, that does not rely on the network file system? Seems 
that if the issue is due to the design of the network file 
system, which we have no control, then embedding some internal 
logic system that handles self-flagging of a lock condition 
seems a reasonable course to pursue.



Scott Doctor
scott at scottdoctor.com
--

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 4 May 2015, at 1:30am, James K. Lowden  wrote:

> That is the way most remote filesystems are designed and implemented
> and documented.  Cf. http://www.ietf.org/rfc/rfc1813.txt:
> 
>   4.11 Caching policies
> 
>   The NFS version 3 protocol does not define a policy for
>   caching on the client or server. In particular, there is no
>   support for strict cache consistency between a client and
>   server, nor between different clients. See [Kazar] for a
>   discussion of the issues of cache synchronization and
>   mechanisms in several distributed file systems.
> 
> If you can find documentation for cache semantics for CIFS, I'd be
> interested to see it.  

In contrast to NFS both SMB and AFP are designed to support networks properly, 
with caches only on the computer which hosts the file and locking correctly 
implemented.  And SQLite, whether it's depending on the locking built into them 
or its own mutex, should work correctly.

The problem is that the network file system implementations don't perform 
according to the design.  I don't work at this level but as I understand it 
there are two main reasons: bugs and speed.  I'm going to ignore bugs here.

The speed problem is that properly supporting locking slows down operations a 
lot.  Instead of one operation you do lock-read-unlock which takes almost three 
times as long.  And that's assuming that the lock succeeds.  So a thing you 
don't do in a client demo is show them the system running single-user, because 
when they get their own multi-user copy of your system they're going to start 
screaming about how slow it is.

So manufacturers at all the drivers in the chain -- OS, network file system, 
disk file system -- tend to implement locking badly to make themselves look 
fast.  Failing to properly handle unlikely cases here and there to save the 
time taken to figure out whether they apply.  And SQLite is incredibly 
intensive on locking and file operations, so it quickly routes out such 
problems.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Stephen Chrzanowski]

TL;DR

Get SQLite away from networks entirely when you start throwing multiple
machines at the problem.  Your last paragraph is probably the best bet if
you're not willing to get into a real client/server setup, and yes, you're
going to run into aggravated users, but, then, which is more important to
deal with?  Aggravated users or functional data?  Training will help with
the aggravated users, as will making your application aware of the
situations is going to be used in and properly deal with the issues that
could come up.  Single user over a wire (Other than IDE, SATA, or SCSI
cables) is "OK", but I'd still not trust any NFS type of communication.  If
you start throwing multiple users with ANY sort of write possibilities,
you're begging for trouble, as you've read in your research.  Read access,
from what I'm told and have read in these very forums is "Alright to do",
but again, I'm still not a fan.

The long story;

At the end of the road you're traveling, you're going to reach the final
conclusion that if you want concurrent users to have access to the data,
AND you want to make sure the data stays valid and not corrupted because of
network file sharing issues, the easiest detour to your final destination
is that you're going to have to get into a real client/server setup and get
it out of your codes mind that a client application should act as a server
as well.

Whether it is with SQLite and you build (IE: Write) a 'server' to handle
the requests and your database stays on the local storage system where the
server is run, or, switch to another program entirely like MySQL.  The
other option is, if you're hooked on SQLite, is to normalize the data to be
worked with on each client into individual files, then when the client is
done with the work, use the SQLite backup routines to push the data back to
the server.  Then, if required, use your merge maintenance to collect all
the resources and drop it into one main data store.  I don't write many
client/server applications for the Win32 environment, but anything I've
written that is web accessible has ALWAYS had a database engine with the
physical files sitting on some drive that is DIRECTLY controlled by the OS
running the database engine.

For desktop apps using SQLite that might want data shared between machines,
I put it in my programs head that any data to be EXCHANGED is done via the
SQLite backup routines.  Modifications are ALWAYS done locally.  If needed,
have the app write a lock file that matches the database file you're
working on, that way any other clients coming to pick the data up look for
that lock file and can behave accordingly, as in, pull the data in as read
only, or, deny access entirely.  First come, first serve policy.  I got
fancy with one of my apps in that I opened up a TCP port in the high 32k
range.  The lock file contained the local IP address of the machine.  If
another client came along to look for the database in question and
encountered the lock file, it'd open up a port to the client and just see
if it still was awake.  Then MS started throwing firewalls around and that
became a fun job to get around.

SQLite is awesome in a server architecture, being used as the back end for
PHP, small to medium web servers, and all the such.  Even with hundreds to
thousands of users hitting the SQLite database files, the file is being
accessed by X number of applications on the local store, or one governing
OS, is handling the file manipulation.  The fact that it is one OS handling
the file manipulation, you'll find that on ANY DBMS (MS-SQL, MySQL,
Postgres, etc) typically are run from a local store.  There are reasons for
this, and you're running into that reason.  I've actually never heard of a
database engine running over any kind of network device, SAN included.

One method I started to develop years ago was a web service that would take
in SQL calls from a client and spit back a formatted table of results.
Problems obviously arose with raw byte data in text results, so, changed it
up so that the server would just generate a SQLite3 DB on the fly and give
that to the client to work with.  The perk on that was that I could get
table normalization with some work on additional parsing what the query
was.  The issue (Which would have been resolved with further development)
was record locking.  The challenge was to decide what records (For example,
consider Insurance Policies, Doctor Records, Personnel files all as
individual record types) were to be locked on the server while the client
dealt with the data, and how long to keep the data locked.

On Sun, May 3, 2015 at 11:20 PM, Scott Doctor  wrote:

>
> I googled file locking issues. Sheesh, The horror stories of programs like
> Quickbooks corrupting data files. Files getting locked when nobody is using
> them (A windows 7 bug that supposedly was fixed) and endless information
> about what not to do, or what does not work. Very little about what does
> work.
>
> My application is 

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 3 May 2015, at 9:09pm, Scott Doctor  wrote:

> Hmm, one for doing my own locking, one against it. As this seems to be an 
> obvious issue in any network, I wonder why the network developers have not 
> appropriately addressed this issue.

They've tried.  With modern operating systems locking is part of the network 
file system -- things like NFS, CIFS/SMB and AFP.  And these depend a great 
deal on three other subsystems: the operating system, the disk file system 
(FAT, NTFS, HFS) and the network system (probably TCP/IP for all of us).  Every 
combination of these has to be tested, with multiple combinations of different 
numbers of computers.  There are a huge number of combinations and with new 
drivers and versions arising daily it takes a plan of continuous testing, not 
just a project with an end-date.

I have high hopes for SMB3.  It was designed recently, with modern issues and 
capabilities in mind.  Maybe it'll be so trustworthy that Dr Hipp can state 
that SQLite works fine with it.  And Apple's AFP+ is long past its Best By 
date, though Apple shows signs of having adopted SMB2 for networking already, 
which suggests it will not bother with its own replacement for AFP+ and just 
call SMB2 or SMB3 an industry standard.

Maybe it's time for an Open, Free (as in beer, with all patents available to 
all), crowd-developed networking file system, designed with modern issues (e.g. 
attacks, encryption, transactions, ACID, massive parallelism) in mind.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[James K. Lowden]

On Sun, 03 May 2015 13:09:43 -0700
Scott Doctor  wrote:

> I wonder why the network developers have not appropriately addressed
> this issue. Looks like I need to research this problem more before
> implementing. 

>From one perspective -- a common use case -- they *have* addressed this
issue.  But first let's be clear on what's going on and why network
filesystems are absolutely, positively unreliable for SQLite.  

Consider a 1-byte file containing the letter 'A'.  Two processes open
it read-write, and read the file.  The first time read(2) is called, the
'A' is read from the server's file into the client NFS (or whatever)
driver. Thereafter, on each machine, each iteration of 

lseek(fd, 0, 0, SEEK_SET);
read(fd, buf, 1);

will read from the filebuffer cache, not from the server, at least for
a while.  

Now the first machine writes 'B' to the file.  On that machine, the
above iteration returns 'B' because that machine's filebuffer cache was
updated when the 'B' was sent to the server.  The first machine's cache
is coherent with the file contents on the server.  

The second machine is *not* notified of the update.  Reads continue to
be satisfied from cache, and the cache continues to contain 'A'.  The
cache is inconsistent with the file contents on the server.  

That is the way most remote filesystems are designed and implemented
and documented.  Cf. http://www.ietf.org/rfc/rfc1813.txt:

   4.11 Caching policies

   The NFS version 3 protocol does not define a policy for
   caching on the client or server. In particular, there is no
   support for strict cache consistency between a client and
   server, nor between different clients. See [Kazar] for a
   discussion of the issues of cache synchronization and
   mechanisms in several distributed file systems.

If you can find documentation for cache semantics for CIFS, I'd be
interested to see it.  

This is why I keep reminding the list that problems with remote
filesystems aren't due to "bugs".  They work as designed and as
documented.  They just don't work as expected, when "expected"
means, "just like local filesystems (only slower)".  

A moment's thought reveals why they work this way.  Network-wide
client-cache coherency is has O(n) complexity where N is the number of
clients.  It's fraught with timing and performance issues.  

We haven't even mentioned SQLite per se.  I don't know where SQLite
tracks table-update status.  I do know that some of the filebuffer
cache data are in SQLite variables.  Some invariants about
thread-of-control consistency of those variables do not hold when the
underlying file is changed by an "external" process, which is what a
SQLite client running on another machine is.  The situation invites
textbook write-after-read cache-coherency problems.  

The problems that SQLite has with network filesystems isn't the least
mysterious if you scratch the surface.  RFC 1813 is in the public
domain, and references several papers that are freely available.  One
evening will convince you.  

You might ask, if the problems are hard but solveable, why not solve
them anyway, and perhaps make the sematics configurable on a
correctness-performance tradeoff?  I think the answer is that most
applications don't require it!  Most remote-file applications read the
entire file into memory, modify the data in memory, and write the file
back out minutes or hours later when the user saves it.  For them,
last-writer-wins is OK.  For applications that need to coordinate
comingled writes to a shared file, the dominant solution is a
server-based DBMS such as Postgres.  

--jkl

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

I googled file locking issues. Sheesh, The horror stories of programs 
like Quickbooks corrupting data files. Files getting locked when nobody 
is using them (A windows 7 bug that supposedly was fixed) and endless 
information about what not to do, or what does not work. Very little 
about what does work.

My application is oriented toward research where data is added, but 
rarely deleted. Doing a cleanup or purge operation is rare, but would be 
done by a single user with a backup made of the original (I automate 
backups since people always forget to do it). So I just instruct the 
user to make sure they are the only one accessing that database when 
doing so. Users always follow directions, um

What I am considering is if two people are adding to the same project 
file at the same time, make a local database that has the new 
information, then do a merge later into the main project file later. 
This is a real situation anyways as data may be collected offsite on 
something like an iPad or laptop then merged into the main database 
later. Isolated operation with no internet access. Would work similar to 
the rules of accounting. Make a transaction log then close the books at 
the end of the day. Seems to work for the past few hundred years for the 
bean counters. If person X needs person Y's new data, they just run the 
merge operation.

Hmm, I can think of multiple problems with this technique, or the 
aggravation the users may encounter. Need to think about it some more,


Scott Doctor
scott at scottdoctor.com

[sqlite] Multiple instances of the same program accessing the same db file

[James K. Lowden]

On Sat, 02 May 2015 19:24:19 -0700
Scott Doctor  wrote:

> Each computer has its own copy of the program, but may 
> share the database file which may be located on any of the computers.
> So I am wondering whether I should implement my own locking logic in
> my program.

Yes, you need to arrange within the application not to have two
processes writing to the remote database at the same time.  The locking
SQLite uses depends on semantics not provided by the remote
filesystem.  

With a local filesystem, when two processes are updating a file, each
process's update is visible to the other in the unified buffer cache
supplied by the OS.  In a networked filesystem, there is no unified
buffer cache: updates from process A, while manifest in the file, are
not necessarily reflected in the cache used by process B on another
machine.  A subsequent update from B based on its outdated cache could
well create an incoherent file image.  

The only safe answer is arrange for each update to begin by locking the
file in the application.  Then open the database, update it, close the
database, and unlock the file.  By the same token, after any update
every reading process should close and re-open the database before
continuing to rely on the database

One way to do that would be to keep an update.count file adjacent to
the database file.  Lock it, read it, increment it, proceed with the
database update, and release it.  Before each read, lock the file for
reading, and check the counter value.  If it's changed, close and
re-open the database, execute the SELECT, and release the file.  

That's just an outline; I might have overlooked something.  The
complexity and pitfalls explain why applications that need
inter-machine consistency connect to a DBMS daemon that manages its
database files locally.  

HTH.  

--jkl

[sqlite] Multiple instances of the same program accessing the same db file

[Keith Medcalf]

> Hmm, one for doing my own locking, one against it. As this seems to be
> an obvious issue in any network, I wonder why the network developers
> have not appropriately addressed this issue. 

They have.  In the early 80's when network filesystems were invented they were 
incredibly slow.  So incredibly slow that it was often more effective to use 
speedy-memo's, interoffice pneumatic post, and secretaries (what are now called 
administrative assistants were then called).  It was realized, however, that in 
fact there were "business rules" which precluded multiple people from updating 
the same things at the same time (this occurred primarily for files on the 
network, at least initially).

By taking advantage of this knowledge, one could make the filesystem 
"optimistic" in is operations -- it could "optimistically" assume that a given 
filesystem object was only ever going to be updated by one person at a time, 
and therefore provide better performance in this case.  If the "optimism" was 
wrong and all hell broke loose, it was blamed on a failure to understand the 
design of the system by the application pedlars.  In order to avoid having 
foolish people do things that simply would not work, the concept of a "shared 
file" became one of a default to "exclusive" use.  Application writers then got 
around this by using "shared read" (which is not a problem) and used local 
working files.  Only the "first opener" got "exlusive write" access to the 
file.  Most applications still work that way to this day.

Then in the 90's (and subsequently) the snake-oil salesman got involved and 
developed all sorts of snake-oil "WAN Optimizers" and other technology which 
improved things for the "single updater" but did nothing for "multiple 
updaters".  The snake-oil got thicker and thicker and it became impossible to 
turn it off.

Then in the 2000's some snake-oil vendors decided that they would allow "shared 
write" and attempted to implement robust methods to make this work.  None of 
which work worth a pinch (or big pile) of male bovine excrement.

This is the situation which exists today.  Only today there is yet more male 
bovine excrement in the form of various snake-oil filesystem acceleration 
technologies which make even "legacy" style (exclusive access) not work 
properly.

Through all this, "local" access to a filesystem object (File) "exclusively" 
worked properly.  This is why there are client/server applications.  The 
"server" part of the application exclusively talks to the local filesystem 
object, and the "client" applications talk exclusively to the "server" portion 
of the application, which makes the changes desired to its local filesystem 
object.  This is how "network applications" are addressed and is the only way 
of addressing the issue which works.

So, if you want to have a shared database located on a machine other than the 
machine on which the database file resides, you need to use a Client/Server 
implementation.  It is impossible (or at least very very very very difficult -- 
with the cost far exceeding the cost of simply buying a client/server system 
database designed for the purpose).  To do otherwise is inviting disaster 
(unless you know precisely and exactly what you are doing).

> Looks like I need to
> research this problem more before implementing. I dislike probability
> games of designs that will work most  of the time, but have a potential
> collision scenario. Such is why so many applications have the occasional
> crash or corruption.

Often that is the result of believing glossy brochures without having the 
underlying fundamental engineering knowledge of how things are implemented in 
the real world. 

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

Hmm, one for doing my own locking, one against it. As this seems to be 
an obvious issue in any network, I wonder why the network developers 
have not appropriately addressed this issue. Looks like I need to 
research this problem more before implementing. I dislike probability 
games of designs that will work most  of the time, but have a potential 
collision scenario. Such is why so many applications have the occasional 
crash or corruption.


Scott Doctor
scott at scottdoctor.com

On 5/3/2015 12:54 PM, James K. Lowden wrote:
> On Sat, 02 May 2015 19:24:19 -0700
> Scott Doctor  wrote:
>
>> Each computer has its own copy of the program, but may
>> share the database file which may be located on any of the computers.
>> So I am wondering whether I should implement my own locking logic in
>> my program.
> Yes, you need to arrange within the application not to have two
> processes writing to the remote database at the same time.  The locking
> SQLite uses depends on semantics not provided by the remote
> filesystem.
>
> With a local filesystem, when two processes are updating a file, each
> process's update is visible to the other in the unified buffer cache
> supplied by the OS.  In a networked filesystem, there is no unified
> buffer cache: updates from process A, while manifest in the file, are
> not necessarily reflected in the cache used by process B on another
> machine.  A subsequent update from B based on its outdated cache could
> well create an incoherent file image.
>
> The only safe answer is arrange for each update to begin by locking the
> file in the application.  Then open the database, update it, close the
> database, and unlock the file.  By the same token, after any update
> every reading process should close and re-open the database before
> continuing to rely on the database
>
> One way to do that would be to keep an update.count file adjacent to
> the database file.  Lock it, read it, increment it, proceed with the
> database update, and release it.  Before each read, lock the file for
> reading, and check the counter value.  If it's changed, close and
> re-open the database, execute the SELECT, and release the file.
>
> That's just an outline; I might have overlooked something.  The
> complexity and pitfalls explain why applications that need
> inter-machine consistency connect to a DBMS daemon that manages its
> database files locally.
>
> HTH.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 3 May 2015, at 3:24am, Scott Doctor  wrote:

> My program runs on windows and mac with a local area network connecting the 
> computers.  Each computer has its own copy of the program, but may share the 
> database file which may be located on any of the computers. So I am wondering 
> whether I should implement my own locking logic in my program.

Nope.  Leave the locking to SQLite.  It understands its needs better than you 
do.

You will probably want to use SMB2 or, if available, SMB3 to share the database 
across your network.  And I agree with the warnings others have given you.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 3 May 2015, at 2:03am, Scott Doctor  wrote:

> To review, after opening the database, issue the PRAGMA busy_timeout =  x, 
> with x being however long I want to wait before aborting.

Yes.  Since you have two copies of one program, I don't need to say this for 
you, but for other people, the PRAGMA needs to be issued by all programs with a 
connection to the database.  The value is associated with the connection, it's 
not stored in the database or the journal.

> I can keep both database handles open at the same time, but need to make sure 
> I finalize the operation before the timeout happens (assuming the other 
> program is also trying to access the database at the same time).

Yes.  Though you shouldn't be intentionally holding a transaction (i.e. a lock) 
open for minutes at a time in a multi-access environment.

Don't try to second-guess the retry schedule.  It's quite possible, for 
instance, that if process B has to retry and backs off for 1000 milliseconds, 
process A has unlocked, locked, unlocked, locked, unlocked, and locked again in 
that time.  So don't try to set your timeout according to what you think can 
happen in one transaction, set a timeout of at least a couple of minutes -- 
long enough that you can be really sure the other process has messed up in a 
big way and have to reluctantly inform your user of that fact.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 3 May 2015, at 12:55am, J Decker  wrote:

> Yes, it really requires only a little additional work on application side.
> The native open will open it in read/write share allow, and handle
> interlocking.
> 
> if you get a result of SQLITE_BUSY you need to retry the operation after a
> short time.

Just to update J a little, you no longer need to handle the retry in your own 
code.  SQLite has its own exponential-backoff-and-retry feature.  You set it up 
using either C code or a PRAGMA, which have identical result:





Do this with your connection handle after you open the database.  Set it to a 
couple of minutes, or however long you want your program to keep retrying 
before failing and reporting an error to the user.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Richard Hipp]

On 5/2/15, Scott Doctor  wrote:
>
> Is the PRAGMA value the retry interval, or the timeout where it
> aborts and reports a failure?
>

The timeout.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

hmm, I am using sqlite as a project file that keeps track of a variety 
of information (usually a couple dozen megabytes in size per project). 
My initial post assumed a single user with a couple windows open. The 
file might be accessed by another user on a local area network. Usually 
no more than a few people at the same time for a specific project file. 
My program runs on windows and mac with a local area network connecting 
the computers.  Each computer has its own copy of the program, but may 
share the database file which may be located on any of the computers. So 
I am wondering whether I should implement my own locking logic in my 
program.

-
Scott Doctor
scott at scottdoctor.com
-

On 5/2/2015 6:10 PM, Scott Robison wrote:
> Since I'm not clear on whether your two or more
> processes are running on the same machine accessing a local drive or on
> multiple machines or over a network, keep in mind that network file systems
> are notoriously bad at the things SQLite needs (locking).

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Robison]

On Sat, May 2, 2015 at 7:03 PM, Scott Doctor  wrote:

>
> To review, after opening the database, issue the PRAGMA busy_timeout =  x,
> with x being however long I want to wait before aborting. I can keep both
> database handles open at the same time, but need to make sure I finalize
> the operation before the timeout happens (assuming the other program is
> also trying to access the database at the same time).
>

I haven't seen anyone give the typical warning about not doing this on a
network file system. Since I'm not clear on whether your two or more
processes are running on the same machine accessing a local drive or on
multiple machines or over a network, keep in mind that network file systems
are notoriously bad at the things SQLite needs (locking). If you haven't
yet read https://www.sqlite.org/whentouse.html it might be a good thing to
review. If I missed something and this is all old hat, my apolgoies for the
repetition.

-- 
Scott Robison

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

To review, after opening the database, issue the PRAGMA busy_timeout =  
x, with x being however long I want to wait before aborting. I can keep 
both database handles open at the same time, but need to make sure I 
finalize the operation before the timeout happens (assuming the other 
program is also trying to access the database at the same time).

-
Scott Doctor
scott at scottdoctor.com
-

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

Is the PRAGMA value the retry interval, or the timeout where it 
aborts and reports a failure?



Scott Doctor
scott at scottdoctor.com
--

On 5/2/2015 5:08 PM, Simon Slavin wrote:
> On 3 May 2015, at 12:55am, J Decker  wrote:
>
>> Yes, it really requires only a little additional work on application side.
>> The native open will open it in read/write share allow, and handle
>> interlocking.
>>
>> if you get a result of SQLITE_BUSY you need to retry the operation after a
>> short time.
> Just to update J a little, you no longer need to handle the retry in your own 
> code.  SQLite has its own exponential-backoff-and-retry feature.  You set it 
> up using either C code or a PRAGMA, which have identical result:
>
> 
>
> 
>
> Do this with your connection handle after you open the database.  Set it to a 
> couple of minutes, or however long you want your program to keep retrying 
> before failing and reporting an error to the user.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Multiple instances of the same program accessing the same db file

[J Decker]

Yes, it really requires only a little additional work on application side.
The native open will open it in read/write share allow, and handle
interlocking.

if you get a result of SQLITE_BUSY you need to retry the operation after a
short time.

On Sat, May 2, 2015 at 4:52 PM, Scott Doctor  wrote:

>
> I am somewhat new to sqlite and am trying to decide an issue with the
> program I am writing (cross platform, written in C/C++). After reading
> through the sqlite documentation, I am still unsure about the issue how to
> implement multiple instances of the same program.
>
> Consider a program that may have more than one instance of the same
> program open at the same time. Both instances need to read/write the same
> sqlite database file.
>
> Can both instances open the same database file at the same time?
> Another way to word the question is whether sqlite will properly handle
> two independent programs accessing the same sqlite database file at the
> same time where both programs will be reading/writing to the database? Or
> do I need to implement or more complex strategy for accessing the sqlite
> file?
>
> --
>
> -
> Scott Doctor
> scott at scottdoctor.com
> -
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

I am somewhat new to sqlite and am trying to decide an issue with the 
program I am writing (cross platform, written in C/C++). After reading 
through the sqlite documentation, I am still unsure about the issue how 
to implement multiple instances of the same program.

Consider a program that may have more than one instance of the same 
program open at the same time. Both instances need to read/write the 
same sqlite database file.

Can both instances open the same database file at the same time?
Another way to word the question is whether sqlite will properly handle 
two independent programs accessing the same sqlite database file at the 
same time where both programs will be reading/writing to the database? 
Or do I need to implement or more complex strategy for accessing the 
sqlite file?

-- 

-
Scott Doctor
scott at scottdoctor.com
-