[Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Fernando M. Roxo da Motta]

On Mon, 7 Oct 2019 16:46:29 +0200, <jaroslaw.sadowski at cpk.pl> wrote:


> Hi everybody,
> 
>  
> 
> Very interesting thread…
> 
> Does the same apply if we only read data from geopackage (not
> overwrite/edit it) on NFS? I have a situation with deadlocks when
> several users are only trying to read data (without competitive
> writing).
> 
> Can anyone confirm this with his case?
> 

  Competitive reading over NFS should pose no problem, whatever the
file format.  If you are getting deadlocks in readonly access it seems
like you can have some other problems in the software accessing (like
pretend to open readonly but set some update flag),  or in the
server/client configuration. There are a number of inherent issues in
NFS that are independent of the data type or software access.

  If we include writing there is another whole universe of problems
with NFS, most of them shared by others network distributed
filesystems.  One huge problem is in the file locking over NFS, it is
not guaranteed that it will work in any way.  It can work sometimes and
not work in other similar situation. Another huge problem is the
latency of the protocol.  NFS, and many other distributed filesystems
over network, are not atomic, in the sense that an interaction that
changes the file content (writing, deleting, ...) or state (locking) is
not sent immediately to the server, besides the characteristic network
latency.   This can lead to many conflicts and race conditions.

  I would suggest that if you have a base of data, whatever the size,
that must be shared and accessed for edition over network for a number
of workers, independent of the number of competitive access, the best
approach is to store those data in a database with a dedicated server
as PostgreSQL or even MySQL/MariaDB).   Edition of data file content
over network don't look like a smart move to me.

  HTH


>  
> 
> greeting from Poland
> 
> Jarosław Sadowski
> 
>  
> 
> From: Qgis-user <qgis-user-bounces at lists.osgeo.org> On Behalf Of
> Jonathan Moules Sent: Monday, October 7, 2019 4:17 PM
> To: Tobias Wendorff <tobias.wendorff at tu-dortmund.de>; Andrea Peri
> <aperi2007 at gmail.com> Cc: qgis-user <qgis-user at lists.osgeo.org>; Paul
> Wittle <paul.wittle at dorsetcouncil.gov.uk> Subject: Re: [Qgis-user]
> GeoPackage deadlocks (Andrea Peri)
> 
>  
> 
> (A little late).
> 
> TL;DR: at least for QGIS, is - never multi-user edit
> SQLite/SpatiaLite/GeoPackages on network file systems.
> 
> SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few
> caveats when it comes to multiple users trying to edit it at once. 
> 
> https://www2.sqlite.org/howtocorrupt.html
> 
> (my bold)
> 
> "SQLite depends on the underlying filesystem to do locking as the
> documentation says it will. But some filesystems contain bugs in
> their locking logic such that the locks do not always behave as
> advertised. This is especially true of network filesystems and NFS in
> particular. If SQLite is used on a filesystem where the locking
> primitives contain bugs, and if two or more threads or processes try
> to access the same database at the same time, then database
> corruption might result."
> 
> And there's also:
> 
> https://www.sqlite.org/whentouse.html
> 
> Put simply (Note: I'm not an expert): It's fine to edit SQLite
> databases if they're not on a network file system with as many users
> as you want, or if they are on a network and you can guarantee only
> one process is going to write. However if multiple people/processes
> want to write to a network file system, you'll need a piece of
> middleware to manage the process, otherwise there's a good chance of
> corruption as Paul is seeing.
> 
> It may also be that QGIS is doing some of the other things on the
> "how to corrupt" page too. I imagine it will only get worse if you
> use multiple different software packages to edit simultaneously.
> 
> Cheers,
> 
> Jonathan
> 
> On 2019-09-27 09:50, Tobias Wendorff wrote:
> 
> Am 27.09.2019 um 10:24 schrieb Andrea Peri
> <mailto:aperi2007 at gmail.com> <aperi2007 at gmail.com>: 
> Have you tried to use spatialite instead of geopackage. ?
> 
>  
> Why not plain SQLite? Nobody needs and uses the spatial functions of
> Spatialite, they are even not part of bloatware GPKG (sorry, the
> created db-files are huge without any compression). The only reason
> is indexing and this could be forked off GPGK and Spatialite. 
> To the topic: I think, it‘s always a bad idea to let multiple users
> work on a single SQLite-based database. It hasn‘t been created for
> this reason.
> 
> 
> 
> 
> 
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org> 
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
> 








  Roxo

-- 
---------------- Non luctari, ludare -------------------+ WYSIWYG
Fernando M. Roxo da Motta <petro at roxo.org>              | Editor?
Except where explicitly stated I speak on my own behalf.|  VI !!
                PU5RXO                                  | I see text,
------------ Quis custodiet ipsos custodes?-------------+ I get text!