[Qgis-user] GeoPackage deadlocks

[Matthias Kuhn]

Hi all,

We had the same experience in a project of ours. Multiple users with 
read-only access to a gpkg on a (Windows) network share. The results 
were freezes on unloading the layer (e.g. closing QGIS or opening 
another project) on system A until system B would have closed the QGIS 
layer there. The behavior seemed surprising to me and on first sight I'd 
have put it more into the category "bug" rather than "feature".

Good news, this behavior could be changed by switching the sqlite 
journal mode to delete (environment variable OGR_SQLITE_JOURNAL=DELETE).

QGIS and OGR use gpkg in WAL mode by default. Fun fact: this is not 
supported on network drives, as documented on 
https://www.sqlite.org/wal.html :

 > All processes using a database must be on the same host computer; WAL 
does not work over a network filesystem

I did not test what happens if only *some* of the accessing systems use 
DELETE and others use WAL, nor did I check what happens when writing to 
the DB while reading (or even concurrent writes - which definitely no 
longer is a safe operation mode for sqlite on a shared network volume <- 
if you end up in this category, get a postgres server).

Maybe we need to rethink the default mode of opening gpkg's
and if anyone can provide ground truth on the untested operation modes 
mentioned before, please share the results here.

Bests, Matthias

On 10/18/19 8:17 AM, Chavoux Luyt wrote:
> Hi,
>
> Just my two cents... this is IMHO exactly where postGIS will shine. 
> Using PostgreSQL as backend will avoid any of these issues (i.e. 
> prevent data corruption). For my personal projects I have never 
> bothered with it, so I cannot say how easy it is to set up, but once 
> it is up and running PostgreSQL is one of the most reliable database 
> servers out there.
>
> Cheers,
> Chavoux
>
> On Thu, 17 Oct 2019, 21:01 , ---------- Forwarded message ----------
>
>     From: Francesco Pelullo <f.pelullo at gmail.com
>     <mailto:f.pelullo at gmail.com>>
>     To: Patrick Dunford <enzedrailmaps at gmail.com
>     <mailto:enzedrailmaps at gmail.com>>
>     Cc: qgis-user at lists.osgeo.org <mailto:qgis-user at lists.osgeo.org>
>     Bcc:
>     Date: Thu, 17 Oct 2019 13:09:43 +0200
>     Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
>     Il gio 17 ott 2019, 12:54 Patrick Dunford <enzedrailmaps at gmail.com
>     <mailto:enzedrailmaps at gmail.com>> ha scritto:
>
>         It's not a network issue and neither is it a Windows issue. Two
>         processes accessing geopackage on a local computer can run
>         into the same
>         issue. SQlite is designed for a single user. It is very
>         reliable when
>         used as 
>
>
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20191025/d4e60e7a/attachment.html>