[Qgis-user] GeoPackage deadlocks

[Jésahel Benoist]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20191025/da876923/attachment.html>