Re: GeoPackage deadlocks

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks

Chavoux Luyt
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 <[hidden email]>
To: Patrick Dunford <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
To: Patrick Dunford <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks

Even Rouault-2
On vendredi 25 octobre 2019 17:10:40 CEST Matthias Kuhn wrote:

> 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).

Caution: WAL was enabled because DELETE (which is the default journal mode of
SQLite) was causing locking issues, even in local file, single user, single
QGIS application context. There might have been later changes in the OGR
provider that makes it no longer needed, but this should be carefully checked.
In particular if the test added in https://github.com/qgis/QGIS/commit/
b6b8759efbeb833d0d3dbf6df008087701361ad3#diff-17fd782357fcfed115b06d24b4416bbdR92
still pass in DELETE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks

djes
I could provide some background information as we're working for about a year in a multi-users (5 users) configuration mainly based on network shared geopackages in a Windows environment. I've tried and tested a lot of configurations and still looking for a near PostGIS solution only in terms of easiness and security. 

At first, we were using SQLite and I have to say that it was good, especially in terms of speed. But it lacked the possibility to alter database schema, which was a pain. 
So I switched to Geopackage and very bad problems started. 
First as you all know was the speed problem. Sometimes, it was just unusable with only two users, and sometimes even with only one user, especially with big database, with some display locks. I discovered that the drawing by itself was like a concurrent user... Disabling the drawing restored usage, even if sometimes it needed to restart qgis.
Then I discovered the wal problem and switched to delete mode. Speed problem was gone but... I got a file lock. One of my db file was then unusable. I had to restore it. Happily it was with the most experienced user and at the end of the day, and we had only one session data lost.
So I decided to divide the biggest db to several little files and to make apply stricter usage rules to users to be sure that there isn't much concurrent access. 
For now it's usable but we still have to restart qgis sometimes. Sometimes the display disappears and reappears with a delay or by creating an entity. Note that it's the same with SQLite.

As a coder, I wonder why there isn't always a file locking mechanism with a threaded waiting loop, even for read access and for all file formats, because if you change the file content, nothing can say a read wasn't done at the same time, and so the read could obtain bad values. Even multiple threads on a single machine could access the same file anytime, so it would be safer to avoid this kind of problem. 

I do love QGIS and would be really happy to help, if I can, with my modest capabilities.

Best regards,

Jésahel


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks

Even Rouault-2
On vendredi 25 octobre 2019 22:51:39 CEST Jésahel Benoist wrote:

> I could provide some background information as we're working for about a
> year in a multi-users (5 users) configuration mainly based on network
> shared geopackages in a Windows environment. I've tried and tested a lot of
> configurations and still looking for a near PostGIS solution only in terms
> of easiness and security.
>
> At first, we were using SQLite and I have to say that it was good,
> especially in terms of speed. But it lacked the possibility to alter
> database schema, which was a pain.
> So I switched to Geopackage and very bad problems started.
> First as you all know was the speed problem. Sometimes, it was just
> unusable with only two users, and sometimes even with only one user,
> especially with big database, with some display locks. I discovered that
> the drawing by itself was like a concurrent user... Disabling the drawing
> restored usage, even if sometimes it needed to restart qgis.
> Then I discovered the wal problem and switched to delete mode. Speed
> problem was gone but... I got a file lock. One of my db file was then
> unusable. I had to restore it. Happily it was with the most experienced
> user and at the end of the day, and we had only one session data lost.
> So I decided to divide the biggest db to several little files and to make
> apply stricter usage rules to users to be sure that there isn't much
> concurrent access.
> For now it's usable but we still have to restart qgis sometimes. Sometimes
> the display disappears and reappears with a delay or by creating an entity.
> Note that it's the same with SQLite.
>
> As a coder, I wonder why there isn't always a file locking mechanism with a
> threaded waiting loop, even for read access and for all file formats,
> because if you change the file content, nothing can say a read wasn't done
> at the same time, and so the read could obtain bad values. Even multiple
> threads on a single machine could access the same file anytime, so it would
> be safer to avoid this kind of problem.

To disable all locking, you can try setting the SQLITE_USE_OGR_VFS environment
variable to YES that will switch to a custom SQLite virtual file system
implementation (designed for GDAL /vsicurl/ mostly, but that can work with
regular files as well), that doesn't take any lock at all. But beware: it will
eat your children if you try to do concurrent edits, and you'll get reading
errors if reading while editing.

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks

Matthias Kuhn 🌍
In reply to this post by Even Rouault-2
On 10/25/19 7:04 PM, Even Rouault wrote:

> On vendredi 25 octobre 2019 17:10:40 CEST Matthias Kuhn wrote:
>> 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).
> Caution: WAL was enabled because DELETE (which is the default journal mode of
> SQLite) was causing locking issues, even in local file, single user, single
> QGIS application context. There might have been later changes in the OGR
> provider that makes it no longer needed, but this should be carefully checked.
> In particular if the test added in https://github.com/qgis/QGIS/commit/
> b6b8759efbeb833d0d3dbf6df008087701361ad3#diff-17fd782357fcfed115b06d24b4416bbdR92
> still pass in DELETE

FYI, after recent experiences with WAL disabled (in a different scenario
without network involved), I don't think the situation has completely
stabilized.

It seems we'll need to evaluate how to deal with multithreading to get
things working properly.

Matthias

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user