[Qgis-user] GeoPackage deadlocks

[Even Rouault]

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