[Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Saber Razmjooei]

Hi,

>
>
>  > 2. Ideally it would be possible for QGIS to prevent multiple edit
> sessions
>
> Multiple edit sessions are absolutely fine, on a *local* filesystem at
> the SQLite file level. It may *not* be fine at the data level: if you
> and I edit the same polygon at the same time and then commit, what
> happens? Which does QGIS keep?


We are working on a library to handle these type of conflict resolution
(currently it supports SQLite based databases):
https://github.com/lutraconsulting/geodiff

Regards
Saber



> That's an application-level problem for
> QGIS (no idea if it's addressed). For the SQLite file itself, *only*
> multiple editors via the network is a problem (well, at the other things
> on that link). It's definitely something QGIS needs to be able to handle
> better / warn about / lock.
>
> I'd suggest raising this second issue on the QGIS tracker as a bug.
>
> Cheers,
>
> Jonathan
>
>
> On 2019-10-07 15:37, Paul Wittle wrote:
> > Hi,
> >
> > I think the issue is not really the downsides of the file format so much
> as the more recent promotion of GeoPackage as a default format over
> shapefiles.
> >
> > In my view it is unreasonable to expect the average user to delve into
> this level of detail and I would personally expect that the software (or
> documentation) would make clear any serious issues such as data corruption
> risks. Personally I’d go further and say work to avoid the situation at all.
> >
> > This is why I asked the question really as there seem to me to be two
> key points:
> >
> >    1.  There is some evidence that read-only access may be editing the
> file, see https://github.com/qgis/QGIS/issues/23991
> >    2.  Ideally it would be possible for QGIS to prevent multiple edit
> sessions
> >
> > IMHO the usefulness of the format seems limited if it is not possible
> for multiple users to view the same data. That said, the bug report I’ve
> linked to shows that other agree and the issue is being dealt with using
> the normal methods.
> >
> > The second point is really the focus of why I originally asked the
> question. It is clear that SQLite itself cannot achieve this as it is
> unaware of edit sessions however QGIS could simply create a sidecar file as
> a worst case and that should work. If the sidecar file exists then editing
> is not possible unless you own the file otherwise the file is available for
> editing.
> >
> > In simple terms I want QGIS to say no (and explain it is already being
> edited in a dialog) if a second user tries to put a GeoPackage in edit mode
> when someone else is already editing it.
> >
> > I’ve already created a section in my bespoke plugin which corrects the
> bad projections for MapInfo tab files so I might just write the
> functionality above myself using Python.
> >
> > Cheers,
> > Paul
> >
> > From: Jonathan Moules <jonathan-lists at lightpear.com>
> > Sent: 07 October 2019 15:17
> > 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 <
> P.Wittle at dorsetcc.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 <aperi2007 at gmail.com><mailto:
> 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
> >
> > This e-mail and any files transmitted with it are intended solely for
> the use of the individual or entity to whom they are addressed. It may
> contain unclassified but sensitive or protectively marked material and
> should be handled accordingly. Unless you are the named addressee (or
> authorised to receive it for the addressee) you may not copy or use it, or
> disclose it to anyone else. If you have received this transmission in error
> please notify the sender immediately. All traffic may be subject to
> recording and/or monitoring in accordance with relevant legislation. Any
> views expressed in this message are those of the individual sender, except
> where the sender specifies and with authority, states them to be the views
> of Dorset Council. Dorset Council does not accept service of documents by
> fax or other electronic means. Virus checking: Whilst all reasonable steps
> have been taken to ensure that this electronic communication and its
> attachments whether encoded, encrypted or otherwise supplied are free from
> computer viruses, Dorset Council accepts no liability in respect of any
> loss, cost, damage or expense suffered as a result of accessing this
> message or any of its attachments. For information on how Dorset Council
> processes your information, please see www.dorsetcouncil.gov.uk/416433
>
>
> _______________________________________________
> Qgis-user mailing list
> 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



-- 
Saber Razmjooei
www.lutraconsulting.co.uk
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20191007/cc80d482/attachment.html>