Hi Jarosław,
> Does the same apply if we only read data from geopackage (not
overwrite/edit it) on NFS? I have a situation with deadlocks when
several users are only trying to read data (without competitive writing).
At the file level for SQLite itself, there are basically no constraints
for reading as far as I know. You can have an effectively infinite
number of readers (or however many your filesystem allow to access a
single file at a time). Per the When to use page
(https://www.sqlite.org/whentouse.html) (my bold):
"High Concurrency
*SQLite supports an unlimited number of simultaneous readers*, but it
will only allow one writer at any instant in time. For many situations,
this is not a problem. Writers queue up. Each application does its
database work quickly and moves on, and no lock lasts for more than a
few dozen milliseconds. But there are some applications that require
more concurrency, and those applications may need to seek a different
solution."
I would suggest your situation is a QGIS/GeoPackage-driver bug/limitation.
Cheers,
Jonathan
On 2019-10-07 15:46, jaroslaw.sadow...@cpk.pl wrote:
Hi everybody,
Very interesting thread…
Does the same apply if we only read data from geopackage (not overwrite/edit
it) on NFS? I have a situation with deadlocks when several users are only
trying to read data (without competitive writing).
Can anyone confirm this with his case?
greeting from Poland
Jarosław Sadowski
From: Qgis-user <qgis-user-boun...@lists.osgeo.org> On Behalf Of Jonathan Moules
Sent: Monday, October 7, 2019 4:17 PM
To: Tobias Wendorff <tobias.wendo...@tu-dortmund.de>; Andrea Peri
<aperi2...@gmail.com>
Cc: qgis-user <qgis-user@lists.osgeo.org>; Paul Wittle
<paul.wit...@dorsetcouncil.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 <mailto:aperi2...@gmail.com>
<aperi2...@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@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org>
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
