Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Jonathan Moules]

> Two processes accessing geopackage on a local computer can run into 
the same issue. SQlite is designed for a single user.


Interesting. SQlite can most definitely handle multiple users - 
certainly reading, and for writing if it's not over the network, I 
expect the processes are breaking one (or more) of the "do this to 
corrupt your database" rules.


On 17/10/2019 11:54, Patrick Dunford wrote:
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 designed.


On 27/09/19 7:13 PM, Paul Wittle wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was 
hoping to hear; it was the conclusion I'd reached myself.


Has this issue been considered given the official move from shapefile 
to geopackage as the default format?


My understanding was that shapefiles could be used over a network and 
whilst multiple people editing was dangerous it did not cause deadlocks.


Combined with the issues relating to MapInfo tab files over a network 
I've got into some hot water over my attempt to get QGIS rolled out 
in our organisation now. I'm going to have to think carefully about 
my next steps as it can be difficult to educate large numbers of 
staff and the two issues mean that people using QGIS incorrectly can 
cause pretty big issues.


To summarise,
  - You open a geopackage and save to the network; someone else comes 
along and opens it in their QGIS and everything looks okay until the 
PCs deadlock...ICT help calls and data corruption may occur.
  - You open a MapInfo tab file from the network and it looks fine 
(accept that it my draw in a user projection). MapInfo user receive 
errors but this is unknown to the QGIS user...ICT help calls result.


Whilst I'm glad the forums have helped to diagnose both behaviours I 
would personally say that the issues pose a bit of a threat to 
software adoption by larger companies and institutions that may be 
using Windows networks and potentially migrating from MapInfo.


In both cases my gut feel is that the best solution might be to look 
into use detection. If GDAL (I assume) can be improved to detect that 
either file type is already open then it might be possible to simply 
ban a second user from opening the file at the same time. This might 
frustrate some users but most importantly it would make the 
application safer from an ICT perspective.


These are of course just my personal opinion from my particular use 
case so please don't be offended by them if you disagree but I'd be 
really happy to hear how others are approaching the issues and/or 
opposing views?


Many thanks,
Paul


___
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



___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Francesco Pelullo]

Il gio 17 ott 2019, 12:54 Patrick Dunford  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 designed.
>

Maybe in next releases, QGIS could add a new function that create a
temporary .gpkg.lock file in same directory of .gpkg file and thus allows
other QGIS instances to open same .gpkg in read only mode.

Ciao
--FP
___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Patrick Dunford]

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


On 27/09/19 7:13 PM, Paul Wittle wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; 
it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to 
geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst 
multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got 
into some hot water over my attempt to get QGIS rolled out in our organisation 
now. I'm going to have to think carefully about my next steps as it can be 
difficult to educate large numbers of staff and the two issues mean that people 
using QGIS incorrectly can cause pretty big issues.

To summarise,
  - You open a geopackage and save to the network; someone else comes along and 
opens it in their QGIS and everything looks okay until the PCs deadlock...ICT 
help calls and data corruption may occur.
  - You open a MapInfo tab file from the network and it looks fine (accept that 
it my draw in a user projection). MapInfo user receive errors but this is 
unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would 
personally say that the issues pose a bit of a threat to software adoption by 
larger companies and institutions that may be using Windows networks and 
potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use 
detection. If GDAL (I assume) can be improved to detect that either file type 
is already open then it might be possible to simply ban a second user from 
opening the file at the same time. This might frustrate some users but most 
importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so 
please don't be offended by them if you disagree but I'd be really happy to 
hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul


___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Fernando M. Roxo da Motta]

On Mon, 7 Oct 2019 16:46:29 +0200,  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?
> 

  Competitive reading over NFS should pose no problem, whatever the
file format.  If you are getting deadlocks in readonly access it seems
like you can have some other problems in the software accessing (like
pretend to open readonly but set some update flag),  or in the
server/client configuration. There are a number of inherent issues in
NFS that are independent of the data type or software access.

  If we include writing there is another whole universe of problems
with NFS, most of them shared by others network distributed
filesystems.  One huge problem is in the file locking over NFS, it is
not guaranteed that it will work in any way.  It can work sometimes and
not work in other similar situation. Another huge problem is the
latency of the protocol.  NFS, and many other distributed filesystems
over network, are not atomic, in the sense that an interaction that
changes the file content (writing, deleting, ...) or state (locking) is
not sent immediately to the server, besides the characteristic network
latency.   This can lead to many conflicts and race conditions.

  I would suggest that if you have a base of data, whatever the size,
that must be shared and accessed for edition over network for a number
of workers, independent of the number of competitive access, the best
approach is to store those data in a database with a dedicated server
as PostgreSQL or even MySQL/MariaDB).   Edition of data file content
over network don't look like a smart move to me.

  HTH


>  
> 
> greeting from Poland
> 
> Jarosław Sadowski
> 
>  
> 
> From: Qgis-user  On Behalf Of
> Jonathan Moules Sent: Monday, October 7, 2019 4:17 PM
> To: Tobias Wendorff ; Andrea Peri
>  Cc: qgis-user ; Paul
> Wittle  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> : 
> 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/

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[jaroslaw.sadowski]

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  On Behalf Of Jonathan Moules
Sent: Monday, October 7, 2019 4:17 PM
To: Tobias Wendorff ; Andrea Peri 

Cc: qgis-user ; Paul Wittle 

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> 
:
 
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Saber Razmjooei]

Hi,

>
>
>  > 2. Ideally it would be possible for QGIS to prevent multiple edit
> sessions
>
> Multiple edit sessions are absolutely fine, on a *local* filesystem at
> the SQLite file level. It may *not* be fine at the data level: if you
> and I edit the same polygon at the same time and then commit, what
> happens? Which does QGIS keep?


We are working on a library to handle these type of conflict resolution
(currently it supports SQLite based databases):
https://github.com/lutraconsulting/geodiff

Regards
Saber



> That's an application-level problem for
> QGIS (no idea if it's addressed). For the SQLite file itself, *only*
> multiple editors via the network is a problem (well, at the other things
> on that link). It's definitely something QGIS needs to be able to handle
> better / warn about / lock.
>
> I'd suggest raising this second issue on the QGIS tracker as a bug.
>
> Cheers,
>
> Jonathan
>
>
> On 2019-10-07 15:37, Paul Wittle wrote:
> > Hi,
> >
> > I think the issue is not really the downsides of the file format so much
> as the more recent promotion of GeoPackage as a default format over
> shapefiles.
> >
> > In my view it is unreasonable to expect the average user to delve into
> this level of detail and I would personally expect that the software (or
> documentation) would make clear any serious issues such as data corruption
> risks. Personally I’d go further and say work to avoid the situation at all.
> >
> > This is why I asked the question really as there seem to me to be two
> key points:
> >
> >1.  There is some evidence that read-only access may be editing the
> file, see https://github.com/qgis/QGIS/issues/23991
> >2.  Ideally it would be possible for QGIS to prevent multiple edit
> sessions
> >
> > IMHO the usefulness of the format seems limited if it is not possible
> for multiple users to view the same data. That said, the bug report I’ve
> linked to shows that other agree and the issue is being dealt with using
> the normal methods.
> >
> > The second point is really the focus of why I originally asked the
> question. It is clear that SQLite itself cannot achieve this as it is
> unaware of edit sessions however QGIS could simply create a sidecar file as
> a worst case and that should work. If the sidecar file exists then editing
> is not possible unless you own the file otherwise the file is available for
> editing.
> >
> > In simple terms I want QGIS to say no (and explain it is already being
> edited in a dialog) if a second user tries to put a GeoPackage in edit mode
> when someone else is already editing it.
> >
> > I’ve already created a section in my bespoke plugin which corrects the
> bad projections for MapInfo tab files so I might just write the
> functionality above myself using Python.
> >
> > Cheers,
> > Paul
> >
> > From: Jonathan Moules 
> > Sent: 07 October 2019 15:17
> > To: Tobias Wendorff ; Andrea Peri <
> aperi2...@gmail.com>
> > Cc: qgis-user ; Paul Wittle <
> p.wit...@dorsetcc.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 als

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Jonathan Moules]

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  On Behalf Of Jonathan Moules
Sent: Monday, October 7, 2019 4:17 PM
To: Tobias Wendorff ; Andrea Peri 

Cc: qgis-user ; Paul Wittle 

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> 
:
  
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Jonathan Moules]

Hi Paul,

I think we're approaching this from opposing directions. It is *exactly* 
the "downsides" of the file format that impose these constraints. The 
problem is that QGIS isn't handling them well which is where we get 
situations like this.


I 100% agree with you about it being unreasonable for users to know 
about this, and QGIS definitely needs to handle it better, especially if 
it's the default format. That's why I came in with the docs to explain 
where the problem probably lies.



> 2. Ideally it would be possible for QGIS to prevent multiple edit 
sessions


Multiple edit sessions are absolutely fine, on a *local* filesystem at 
the SQLite file level. It may *not* be fine at the data level: if you 
and I edit the same polygon at the same time and then commit, what 
happens? Which does QGIS keep? That's an application-level problem for 
QGIS (no idea if it's addressed). For the SQLite file itself, *only* 
multiple editors via the network is a problem (well, at the other things 
on that link). It's definitely something QGIS needs to be able to handle 
better / warn about / lock.


I'd suggest raising this second issue on the QGIS tracker as a bug.

Cheers,

Jonathan


On 2019-10-07 15:37, Paul Wittle wrote:

Hi,

I think the issue is not really the downsides of the file format so much as the 
more recent promotion of GeoPackage as a default format over shapefiles.

In my view it is unreasonable to expect the average user to delve into this 
level of detail and I would personally expect that the software (or 
documentation) would make clear any serious issues such as data corruption 
risks. Personally I’d go further and say work to avoid the situation at all.

This is why I asked the question really as there seem to me to be two key 
points:

   1.  There is some evidence that read-only access may be editing the file, 
see https://github.com/qgis/QGIS/issues/23991
   2.  Ideally it would be possible for QGIS to prevent multiple edit sessions

IMHO the usefulness of the format seems limited if it is not possible for 
multiple users to view the same data. That said, the bug report I’ve linked to 
shows that other agree and the issue is being dealt with using the normal 
methods.

The second point is really the focus of why I originally asked the question. It 
is clear that SQLite itself cannot achieve this as it is unaware of edit 
sessions however QGIS could simply create a sidecar file as a worst case and 
that should work. If the sidecar file exists then editing is not possible 
unless you own the file otherwise the file is available for editing.

In simple terms I want QGIS to say no (and explain it is already being edited 
in a dialog) if a second user tries to put a GeoPackage in edit mode when 
someone else is already editing it.

I’ve already created a section in my bespoke plugin which corrects the bad 
projections for MapInfo tab files so I might just write the functionality above 
myself using Python.

Cheers,
Paul

From: Jonathan Moules 
Sent: 07 October 2019 15:17
To: Tobias Wendorff ; Andrea Peri 

Cc: qgis-user ; Paul Wittle 

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>:



Have you tried to use spatialite instead of geopackage. ?



Why not plain

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi,

I think the issue is not really the downsides of the file format so much as the 
more recent promotion of GeoPackage as a default format over shapefiles.

In my view it is unreasonable to expect the average user to delve into this 
level of detail and I would personally expect that the software (or 
documentation) would make clear any serious issues such as data corruption 
risks. Personally I’d go further and say work to avoid the situation at all.

This is why I asked the question really as there seem to me to be two key 
points:

  1.  There is some evidence that read-only access may be editing the file, see 
https://github.com/qgis/QGIS/issues/23991
  2.  Ideally it would be possible for QGIS to prevent multiple edit sessions

IMHO the usefulness of the format seems limited if it is not possible for 
multiple users to view the same data. That said, the bug report I’ve linked to 
shows that other agree and the issue is being dealt with using the normal 
methods.

The second point is really the focus of why I originally asked the question. It 
is clear that SQLite itself cannot achieve this as it is unaware of edit 
sessions however QGIS could simply create a sidecar file as a worst case and 
that should work. If the sidecar file exists then editing is not possible 
unless you own the file otherwise the file is available for editing.

In simple terms I want QGIS to say no (and explain it is already being edited 
in a dialog) if a second user tries to put a GeoPackage in edit mode when 
someone else is already editing it.

I’ve already created a section in my bespoke plugin which corrects the bad 
projections for MapInfo tab files so I might just write the functionality above 
myself using Python.

Cheers,
Paul

From: Jonathan Moules 
Sent: 07 October 2019 15:17
To: Tobias Wendorff ; Andrea Peri 

Cc: qgis-user ; Paul Wittle 

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>:



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

This e-mail and any files transmitted with it are intended solely for the use 
of the individual or entity to whom they are addressed. It may contain 
unclassified but sensitive or protectively marked material and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify the 
sender immediately. All traffic may be subject to recording and/or monitoring 
in accordance with relevant legislation. Any views expressed in this messag

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Jonathan Moules]

(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 :

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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi,

I think my perspective is more related to whether or not the documentation / 
software is guiding the users to the right conclusion. It is clear that 
multiple users should not be editing the same file but my original question had 
two points really:

  *   Does the user/software know someone else is already editing?
  *   Should the software permit it at all?

My current thought is that QGIS probably doesn’t know how many people are 
reading or writing to the file and so it is unable to give the user any 
messages that would drive good practice. I’m unclear myself whether the issue 
is caused by two editors or just one editor and one or more reader. I’d have to 
do more testing on that but basically it would be great if it just popped up 
and said you can’t edit this file as someone else has it open for editing.

That hopefully sounds good but the bigger issue appears to be how do you do it?

Paul

P.S. I have heard about the compression issue but I was hoping that will be 
resolved in the future given the prominence given to the format but the OGC.



From: Luigi Pirelli 
Sent: 27 September 2019 09:56
To: Tobias Wendorff 
Cc: Andrea Peri ; qgis-user ; 
Paul Wittle 
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

I suppose the question by Andrea was related if using a different qgis provider 
continue to generate the deadlock

Luigi Pirelli

**
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd 
Edition<https://www.packtpub.com/eu/application-development/mastering-geospatial-development-qgis-3x-third-edition>
* Hire a team: http://www.qcooperative.net
**


On Fri, 27 Sep 2019 at 10:50, Tobias Wendorff 
mailto:tobias.wendo...@tu-dortmund.de>> wrote:
Am 27.09.2019 um 10:24 schrieb Andrea Peri 
mailto: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
This e-mail and any files transmitted with it are intended solely for the use 
of the individual or entity to whom they are addressed. It may contain 
unclassified but sensitive or protectively marked material and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify the 
sender immediately. All traffic may be subject to recording and/or monitoring 
in accordance with relevant legislation. Any views expressed in this message 
are those of the individual sender, except where the sender specifies and with 
authority, states them to be the views of Dorset Council. Dorset Council does 
not accept service of documents by fax or other electronic means. Virus 
checking: Whilst all reasonable steps have been taken to ensure that this 
electronic communication and its attachments whether encoded, encrypted or 
otherwise supplied are free from computer viruses, Dorset Council accepts no 
liability in respect of any loss, cost, damage or expense suffered as a result 
of accessing this message or any of its attachments. For information on how 
Dorset Council processes your information, please see 
www.dorsetcouncil.gov.uk/416433
___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Luigi Pirelli]

I suppose the question by Andrea was related if using a different qgis
provider continue to generate the deadlock

Luigi Pirelli

**
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition

* Hire a team: http://www.qcooperative.net
**


On Fri, 27 Sep 2019 at 10:50, Tobias Wendorff <
tobias.wendo...@tu-dortmund.de> wrote:

> Am 27.09.2019 um 10:24 schrieb Andrea Peri :
>
> 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
> 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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Tobias Wendorff]

> Am 27.09.2019 um 10:24 schrieb Andrea Peri :
> 
> 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
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi,

My understanding is that GeoPackage and Spatialite are  both based on SQLite so 
I’ve been assuming both may be susceptible to this bug?

Paul

From: Andrea Peri 
Sent: 27 September 2019 09:25
To: Paul Wittle 
Cc: Gino Pirelli ; qgis-user 
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

Have you tried to use spatialite instead of geopackage. ?

Il ven 27 set 2019, 10:06 Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> ha 
scritto:
Hi,

We have a plan to migrate from Oracle to PostGIS but there is a lot of work to 
move other associated systems.

I should point out we did not move from Oracle to GeoPackage; we use Oracle for 
other systems. We are migrating users from MapInfo to QGIS and so I told the 
users to keep using the tab files at the start. This resulted in MapInfo errors 
and got me in hot water so I suggested they use GeoPackage instead as that is 
the official data type. Unfortunately that then resulted in the file deadlocks 
so I developed a custom python plugin to go with our install of QGIS and used 
that to give access to some (but not all) datasets using our existing Oracle 
spatial database.

I hope that provides sufficient background but you can probably see it’s not 
going well really as I’ve had to change my advice three times already.
Paul

From: Luigi Pirelli mailto:lui...@gmail.com>>
Sent: 27 September 2019 08:59
To: Paul Wittle mailto:p.wit...@dorsetcc.gov.uk>>
Cc: Alessandro Pasotti mailto:apaso...@gmail.com>>; 
qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

without resting importance to the issue, reading the bug report, I can read you 
moved from oracle spatial to geopackage! why not postgis?

Luigi Pirelli

**
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd 
Edition<https://www.packtpub.com/eu/application-development/mastering-geospatial-development-qgis-3x-third-edition>
* Hire a team: http://www.qcooperative.net
**


On Fri, 27 Sep 2019 at 09:39, Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> 
wrote:
Hi,

I couldn’t see a bug report which I thought matched so I have added one as 
requests but please accept my apologies if it does turn out to be a duplicate 
or if my use of the word deadlock is incorrect.

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

It would be great if others that have experienced the issue could provide any 
further relevant comments as I’ve not got it as well documented as I’d like. 
Sorry, time constraints in the office.

Many thanks,
Paul


From: Alessandro Pasotti mailto:apaso...@gmail.com>>
Sent: 27 September 2019 08:20
To: Paul Wittle mailto:p.wit...@dorsetcc.gov.uk>>
Cc: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)


On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> 
wrote:
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; 
it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to 
geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst 
multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got 
into some hot water over my attempt to get QGIS rolled out in our organisation 
now. I'm going to have to think carefully about my next steps as it can be 
difficult to educate large numbers of staff and the two issues mean that people 
using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and 
opens it in their QGIS and everything looks okay until the PCs deadlock...ICT 
help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that 
it my draw in a user projection). MapInfo user receive errors but this is 
unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would 
personally say that the issues pose a bit of a threat to software adoption by 
larger companies and institutions that may be using Windows networks and 
potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use 
detection. If GDAL (I assume) can be improved to detect that either file type 
is already open then it might be po

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Andrea Peri]

Have you tried to use spatialite instead of geopackage. ?

Il ven 27 set 2019, 10:06 Paul Wittle  ha
scritto:

> Hi,
>
>
>
> We have a plan to migrate from Oracle to PostGIS but there is a lot of
> work to move other associated systems.
>
>
>
> I should point out we did not move from Oracle to GeoPackage; we use
> Oracle for other systems. We are migrating users from MapInfo to QGIS and
> so I told the users to keep using the tab files at the start. This resulted
> in MapInfo errors and got me in hot water so I suggested they use
> GeoPackage instead as that is the official data type. Unfortunately that
> then resulted in the file deadlocks so I developed a custom python plugin
> to go with our install of QGIS and used that to give access to some (but
> not all) datasets using our existing Oracle spatial database.
>
>
>
> I hope that provides sufficient background but you can probably see it’s
> not going well really as I’ve had to change my advice three times already.
>
> Paul
>
>
>
> *From:* Luigi Pirelli 
> *Sent:* 27 September 2019 08:59
> *To:* Paul Wittle 
> *Cc:* Alessandro Pasotti ; qgis-user@lists.osgeo.org
> *Subject:* Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
>
> without resting importance to the issue, reading the bug report, I can
> read you moved from oracle spatial to geopackage! why not postgis?
>
>
>
> Luigi Pirelli
>
>
> **
> * LinkedIn: https://www.linkedin.com/in/luigipirelli
> * Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
> * GitHub: https://github.com/luipir
> * Book: Mastering QGIS3 - 3rd Edition
> <https://www.packtpub.com/eu/application-development/mastering-geospatial-development-qgis-3x-third-edition>
>
> * Hire a team: http://www.qcooperative.net
>
> **
>
>
>
>
>
> On Fri, 27 Sep 2019 at 09:39, Paul Wittle <
> paul.wit...@dorsetcouncil.gov.uk> wrote:
>
> Hi,
>
>
>
> I couldn’t see a bug report which I thought matched so I have added one as
> requests but please accept my apologies if it does turn out to be a
> duplicate or if my use of the word deadlock is incorrect.
>
>
>
> The ticket can be found at https://github.com/qgis/QGIS/issues/32034
>
>
>
> It would be great if others that have experienced the issue could provide
> any further relevant comments as I’ve not got it as well documented as I’d
> like. Sorry, time constraints in the office.
>
>
>
> Many thanks,
>
> Paul
>
>
>
>
>
> *From:* Alessandro Pasotti 
> *Sent:* 27 September 2019 08:20
> *To:* Paul Wittle 
> *Cc:* qgis-user@lists.osgeo.org
> *Subject:* Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
>
>
>
> On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <
> paul.wit...@dorsetcouncil.gov.uk> wrote:
>
> Hi Andrea and Enrico,
>
> Thank you for providing an answer even if it was not what I was hoping to
> hear; it was the conclusion I'd reached myself.
>
> Has this issue been considered given the official move from shapefile to
> geopackage as the default format?
>
> My understanding was that shapefiles could be used over a network and
> whilst multiple people editing was dangerous it did not cause deadlocks.
>
> Combined with the issues relating to MapInfo tab files over a network I've
> got into some hot water over my attempt to get QGIS rolled out in our
> organisation now. I'm going to have to think carefully about my next steps
> as it can be difficult to educate large numbers of staff and the two issues
> mean that people using QGIS incorrectly can cause pretty big issues.
>
> To summarise,
>  - You open a geopackage and save to the network; someone else comes along
> and opens it in their QGIS and everything looks okay until the PCs
> deadlock...ICT help calls and data corruption may occur.
>  - You open a MapInfo tab file from the network and it looks fine (accept
> that it my draw in a user projection). MapInfo user receive errors but this
> is unknown to the QGIS user...ICT help calls result.
>
> Whilst I'm glad the forums have helped to diagnose both behaviours I would
> personally say that the issues pose a bit of a threat to software adoption
> by larger companies and institutions that may be using Windows networks and
> potentially migrating from MapInfo.
>
> In both cases my gut feel is that the best solution might be to look into
> use detection. If GDAL (I assume) can be improved to detect that either
> file type is already open then 

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi,

We have a plan to migrate from Oracle to PostGIS but there is a lot of work to 
move other associated systems.

I should point out we did not move from Oracle to GeoPackage; we use Oracle for 
other systems. We are migrating users from MapInfo to QGIS and so I told the 
users to keep using the tab files at the start. This resulted in MapInfo errors 
and got me in hot water so I suggested they use GeoPackage instead as that is 
the official data type. Unfortunately that then resulted in the file deadlocks 
so I developed a custom python plugin to go with our install of QGIS and used 
that to give access to some (but not all) datasets using our existing Oracle 
spatial database.

I hope that provides sufficient background but you can probably see it’s not 
going well really as I’ve had to change my advice three times already.
Paul

From: Luigi Pirelli 
Sent: 27 September 2019 08:59
To: Paul Wittle 
Cc: Alessandro Pasotti ; qgis-user@lists.osgeo.org
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

without resting importance to the issue, reading the bug report, I can read you 
moved from oracle spatial to geopackage! why not postgis?

Luigi Pirelli

**
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd 
Edition<https://www.packtpub.com/eu/application-development/mastering-geospatial-development-qgis-3x-third-edition>
* Hire a team: http://www.qcooperative.net
**


On Fri, 27 Sep 2019 at 09:39, Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> 
wrote:
Hi,

I couldn’t see a bug report which I thought matched so I have added one as 
requests but please accept my apologies if it does turn out to be a duplicate 
or if my use of the word deadlock is incorrect.

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

It would be great if others that have experienced the issue could provide any 
further relevant comments as I’ve not got it as well documented as I’d like. 
Sorry, time constraints in the office.

Many thanks,
Paul


From: Alessandro Pasotti mailto:apaso...@gmail.com>>
Sent: 27 September 2019 08:20
To: Paul Wittle mailto:p.wit...@dorsetcc.gov.uk>>
Cc: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)


On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> 
wrote:
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; 
it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to 
geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst 
multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got 
into some hot water over my attempt to get QGIS rolled out in our organisation 
now. I'm going to have to think carefully about my next steps as it can be 
difficult to educate large numbers of staff and the two issues mean that people 
using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and 
opens it in their QGIS and everything looks okay until the PCs deadlock...ICT 
help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that 
it my draw in a user projection). MapInfo user receive errors but this is 
unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would 
personally say that the issues pose a bit of a threat to software adoption by 
larger companies and institutions that may be using Windows networks and 
potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use 
detection. If GDAL (I assume) can be improved to detect that either file type 
is already open then it might be possible to simply ban a second user from 
opening the file at the same time. This might frustrate some users but most 
importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so 
please don't be offended by them if you disagree but I'd be really happy to 
hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul


Hi Paul,

A deadlock is a QGIS bug (and quite a serious one).

Can you please file an issue on https://github.com/qgis/QGIS/issues ?
Please check if it's not there, 

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Luigi Pirelli]

without resting importance to the issue, reading the bug report, I can read
you moved from oracle spatial to geopackage! why not postgis?

Luigi Pirelli

**
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition
<https://www.packtpub.com/eu/application-development/mastering-geospatial-development-qgis-3x-third-edition>
* Hire a team: http://www.qcooperative.net
**


On Fri, 27 Sep 2019 at 09:39, Paul Wittle 
wrote:

> Hi,
>
>
>
> I couldn’t see a bug report which I thought matched so I have added one as
> requests but please accept my apologies if it does turn out to be a
> duplicate or if my use of the word deadlock is incorrect.
>
>
>
> The ticket can be found at https://github.com/qgis/QGIS/issues/32034
>
>
>
> It would be great if others that have experienced the issue could provide
> any further relevant comments as I’ve not got it as well documented as I’d
> like. Sorry, time constraints in the office.
>
>
>
> Many thanks,
>
> Paul
>
>
>
>
>
> *From:* Alessandro Pasotti 
> *Sent:* 27 September 2019 08:20
> *To:* Paul Wittle 
> *Cc:* qgis-user@lists.osgeo.org
> *Subject:* Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
>
>
>
> On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <
> paul.wit...@dorsetcouncil.gov.uk> wrote:
>
> Hi Andrea and Enrico,
>
> Thank you for providing an answer even if it was not what I was hoping to
> hear; it was the conclusion I'd reached myself.
>
> Has this issue been considered given the official move from shapefile to
> geopackage as the default format?
>
> My understanding was that shapefiles could be used over a network and
> whilst multiple people editing was dangerous it did not cause deadlocks.
>
> Combined with the issues relating to MapInfo tab files over a network I've
> got into some hot water over my attempt to get QGIS rolled out in our
> organisation now. I'm going to have to think carefully about my next steps
> as it can be difficult to educate large numbers of staff and the two issues
> mean that people using QGIS incorrectly can cause pretty big issues.
>
> To summarise,
>  - You open a geopackage and save to the network; someone else comes along
> and opens it in their QGIS and everything looks okay until the PCs
> deadlock...ICT help calls and data corruption may occur.
>  - You open a MapInfo tab file from the network and it looks fine (accept
> that it my draw in a user projection). MapInfo user receive errors but this
> is unknown to the QGIS user...ICT help calls result.
>
> Whilst I'm glad the forums have helped to diagnose both behaviours I would
> personally say that the issues pose a bit of a threat to software adoption
> by larger companies and institutions that may be using Windows networks and
> potentially migrating from MapInfo.
>
> In both cases my gut feel is that the best solution might be to look into
> use detection. If GDAL (I assume) can be improved to detect that either
> file type is already open then it might be possible to simply ban a second
> user from opening the file at the same time. This might frustrate some
> users but most importantly it would make the application safer from an ICT
> perspective.
>
> These are of course just my personal opinion from my particular use case
> so please don't be offended by them if you disagree but I'd be really happy
> to hear how others are approaching the issues and/or opposing views?
>
> Many thanks,
> Paul
>
>
>
>
>
> Hi Paul,
>
>
>
> A deadlock is a QGIS bug (and quite a serious one).
>
>
>
> Can you please file an issue on https://github.com/qgis/QGIS/issues ?
>
> Please check if it's not there, and if it is, you can add your comments to
> the existing issue.
>
>
>
> Thanks
>
>
>
> --
>
> Alessandro Pasotti
> w3:   www.itopen.it
> This e-mail and any files transmitted with it are intended solely for the
> use of the individual or entity to whom they are addressed. It may contain
> unclassified but sensitive or protectively marked material and should be
> handled accordingly. Unless you are the named addressee (or authorised to
> receive it for the addressee) you may not copy or use it, or disclose it to
> anyone else. If you have received this transmission in error please notify
> the sender immediately. All traffic may be subject to recording and/o

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi,

I couldn’t see a bug report which I thought matched so I have added one as 
requests but please accept my apologies if it does turn out to be a duplicate 
or if my use of the word deadlock is incorrect.

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

It would be great if others that have experienced the issue could provide any 
further relevant comments as I’ve not got it as well documented as I’d like. 
Sorry, time constraints in the office.

Many thanks,
Paul


From: Alessandro Pasotti 
Sent: 27 September 2019 08:20
To: Paul Wittle 
Cc: qgis-user@lists.osgeo.org
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)


On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle 
mailto:paul.wit...@dorsetcouncil.gov.uk>> 
wrote:
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; 
it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to 
geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst 
multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got 
into some hot water over my attempt to get QGIS rolled out in our organisation 
now. I'm going to have to think carefully about my next steps as it can be 
difficult to educate large numbers of staff and the two issues mean that people 
using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and 
opens it in their QGIS and everything looks okay until the PCs deadlock...ICT 
help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that 
it my draw in a user projection). MapInfo user receive errors but this is 
unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would 
personally say that the issues pose a bit of a threat to software adoption by 
larger companies and institutions that may be using Windows networks and 
potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use 
detection. If GDAL (I assume) can be improved to detect that either file type 
is already open then it might be possible to simply ban a second user from 
opening the file at the same time. This might frustrate some users but most 
importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so 
please don't be offended by them if you disagree but I'd be really happy to 
hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul


Hi Paul,

A deadlock is a QGIS bug (and quite a serious one).

Can you please file an issue on https://github.com/qgis/QGIS/issues ?
Please check if it's not there, and if it is, you can add your comments to the 
existing issue.

Thanks

--
Alessandro Pasotti
w3:   www.itopen.it<http://www.itopen.it>
This e-mail and any files transmitted with it are intended solely for the use 
of the individual or entity to whom they are addressed. It may contain 
unclassified but sensitive or protectively marked material and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify the 
sender immediately. All traffic may be subject to recording and/or monitoring 
in accordance with relevant legislation. Any views expressed in this message 
are those of the individual sender, except where the sender specifies and with 
authority, states them to be the views of Dorset Council. Dorset Council does 
not accept service of documents by fax or other electronic means. Virus 
checking: Whilst all reasonable steps have been taken to ensure that this 
electronic communication and its attachments whether encoded, encrypted or 
otherwise supplied are free from computer viruses, Dorset Council accepts no 
liability in respect of any loss, cost, damage or expense suffered as a result 
of accessing this message or any of its attachments. For information on how 
Dorset Council processes your information, please see 
www.dorsetcouncil.gov.uk/416433
___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Andrea Peri]

>In both cases my gut feel is that the best solution might
>be to look into use detection. If GDAL (I assume) can be
>improved to detect that either file type is already open
>then it might be possible to simply ban a second user
>from opening the file at the same time. This might
>frustrate some users but most importantly it would
>make the application safer from an ICT perspective.

Hi, afaik, internet and http is a sessionless protocol so i guess is quite
impossible to gdal understand that an user (another user) is using a file.
To have this kind of communication is use a messaging queue manager like
jboss.

This is my personal opinion.
Regards,
Andrea.



Il ven 27 set 2019, 09:13 Paul Wittle  ha
scritto:

> Hi Andrea and Enrico,
>
> Thank you for providing an answer even if it was not what I was hoping to
> hear; it was the conclusion I'd reached myself.
>
> Has this issue been considered given the official move from shapefile to
> geopackage as the default format?
>
> My understanding was that shapefiles could be used over a network and
> whilst multiple people editing was dangerous it did not cause deadlocks.
>
> Combined with the issues relating to MapInfo tab files over a network I've
> got into some hot water over my attempt to get QGIS rolled out in our
> organisation now. I'm going to have to think carefully about my next steps
> as it can be difficult to educate large numbers of staff and the two issues
> mean that people using QGIS incorrectly can cause pretty big issues.
>
> To summarise,
>  - You open a geopackage and save to the network; someone else comes along
> and opens it in their QGIS and everything looks okay until the PCs
> deadlock...ICT help calls and data corruption may occur.
>  - You open a MapInfo tab file from the network and it looks fine (accept
> that it my draw in a user projection). MapInfo user receive errors but this
> is unknown to the QGIS user...ICT help calls result.
>
> Whilst I'm glad the forums have helped to diagnose both behaviours I would
> personally say that the issues pose a bit of a threat to software adoption
> by larger companies and institutions that may be using Windows networks and
> potentially migrating from MapInfo.
>
> In both cases my gut feel is that the best solution might be to look into
> use detection. If GDAL (I assume) can be improved to detect that either
> file type is already open then it might be possible to simply ban a second
> user from opening the file at the same time. This might frustrate some
> users but most importantly it would make the application safer from an ICT
> perspective.
>
> These are of course just my personal opinion from my particular use case
> so please don't be offended by them if you disagree but I'd be really happy
> to hear how others are approaching the issues and/or opposing views?
>
> Many thanks,
> Paul
>
> Message: 4
> Date: Thu, 26 Sep 2019 11:10:45 +0200
> From: Andrea Peri 
> To: Enrico Fiore 
> Cc: qgis-user 
> Subject: Re: [Qgis-user] GeoPackage deadlocks
> Message-ID:
> <
> cabqtjk-1hd9atuvhtiw81zw7xe_kpefut7bjcxxgsz0me56...@mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi,
> Geopackage is an Exchange format. Is not affordable in a multiuser data
> entry environment.
> This require a dbms like postgres + postfis.
>
> Regards,
> Andrea.
>
>
> Il gio 26 set 2019, 10:18 Enrico Fiore  ha scritto:
>
> > Hi,
> >
> > Is anyone else getting occasional deadlocks when using GeoPackages in
> > the Long Term Release of QGIS?
> >
> > We are using them over a Windows network so there are multiple users
> > trying to access the same GeoPackage files. It all seems fine and two
> > or more can open the file to look at but when someone edits it you
> > sometimes hit a deadlock where none of the QGIS applications will shut
> down.
> >
> > I have the same issue and the same scenario. Multiple users that
> > trying to access a the same GeoPackage file located in a server causes
> QGIS deadlock.
> >
> > The issue rises also if no geopackage layer are in editing mode, but
> > simply loaded in one QGIS view.
> >
> > There is a solution for this behaviour or is this a bug?
> >
> > Regards,
> >
> >
> > Enrico
> > ___
> > 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
> -- next part --
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/qgis-user/attachments/20190926/10ada3cf/attachment-0001.html
> >
>
> --
>
> This e-mail and any files transmitted with it are intended solely for the
> use of the individual or entity to whom they are addressed. It may contain
> unclassified but sensitive or protectively marked material and should be
> handled accordingly. Unless you are the named addressee (or authorised to
> receive it for the addresse

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Alessandro Pasotti]

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <
paul.wit...@dorsetcouncil.gov.uk> wrote:

> Hi Andrea and Enrico,
>
> Thank you for providing an answer even if it was not what I was hoping to
> hear; it was the conclusion I'd reached myself.
>
> Has this issue been considered given the official move from shapefile to
> geopackage as the default format?
>
> My understanding was that shapefiles could be used over a network and
> whilst multiple people editing was dangerous it did not cause deadlocks.
>
> Combined with the issues relating to MapInfo tab files over a network I've
> got into some hot water over my attempt to get QGIS rolled out in our
> organisation now. I'm going to have to think carefully about my next steps
> as it can be difficult to educate large numbers of staff and the two issues
> mean that people using QGIS incorrectly can cause pretty big issues.
>
> To summarise,
>  - You open a geopackage and save to the network; someone else comes along
> and opens it in their QGIS and everything looks okay until the PCs
> deadlock...ICT help calls and data corruption may occur.
>  - You open a MapInfo tab file from the network and it looks fine (accept
> that it my draw in a user projection). MapInfo user receive errors but this
> is unknown to the QGIS user...ICT help calls result.
>
> Whilst I'm glad the forums have helped to diagnose both behaviours I would
> personally say that the issues pose a bit of a threat to software adoption
> by larger companies and institutions that may be using Windows networks and
> potentially migrating from MapInfo.
>
> In both cases my gut feel is that the best solution might be to look into
> use detection. If GDAL (I assume) can be improved to detect that either
> file type is already open then it might be possible to simply ban a second
> user from opening the file at the same time. This might frustrate some
> users but most importantly it would make the application safer from an ICT
> perspective.
>
> These are of course just my personal opinion from my particular use case
> so please don't be offended by them if you disagree but I'd be really happy
> to hear how others are approaching the issues and/or opposing views?
>
> Many thanks,
> Paul



Hi Paul,

A deadlock is a QGIS bug (and quite a serious one).

Can you please file an issue on https://github.com/qgis/QGIS/issues ?
Please check if it's not there, and if it is, you can add your comments to
the existing issue.

Thanks

-- 
Alessandro Pasotti
w3:   www.itopen.it
___
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

Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

[Paul Wittle]

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; 
it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to 
geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst 
multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got 
into some hot water over my attempt to get QGIS rolled out in our organisation 
now. I'm going to have to think carefully about my next steps as it can be 
difficult to educate large numbers of staff and the two issues mean that people 
using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and 
opens it in their QGIS and everything looks okay until the PCs deadlock...ICT 
help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that 
it my draw in a user projection). MapInfo user receive errors but this is 
unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would 
personally say that the issues pose a bit of a threat to software adoption by 
larger companies and institutions that may be using Windows networks and 
potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use 
detection. If GDAL (I assume) can be improved to detect that either file type 
is already open then it might be possible to simply ban a second user from 
opening the file at the same time. This might frustrate some users but most 
importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so 
please don't be offended by them if you disagree but I'd be really happy to 
hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

Message: 4
Date: Thu, 26 Sep 2019 11:10:45 +0200
From: Andrea Peri 
To: Enrico Fiore 
Cc: qgis-user 
Subject: Re: [Qgis-user] GeoPackage deadlocks
Message-ID:

Content-Type: text/plain; charset="utf-8"

Hi,
Geopackage is an Exchange format. Is not affordable in a multiuser data entry 
environment.
This require a dbms like postgres + postfis.

Regards,
Andrea.


Il gio 26 set 2019, 10:18 Enrico Fiore  ha scritto:

> Hi,
>
> Is anyone else getting occasional deadlocks when using GeoPackages in
> the Long Term Release of QGIS?
>
> We are using them over a Windows network so there are multiple users
> trying to access the same GeoPackage files. It all seems fine and two
> or more can open the file to look at but when someone edits it you
> sometimes hit a deadlock where none of the QGIS applications will shut down.
>
> I have the same issue and the same scenario. Multiple users that
> trying to access a the same GeoPackage file located in a server causes QGIS 
> deadlock.
>
> The issue rises also if no geopackage layer are in editing mode, but
> simply loaded in one QGIS view.
>
> There is a solution for this behaviour or is this a bug?
>
> Regards,
>
>
> Enrico
> ___
> 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
-- next part --
An HTML attachment was scrubbed...
URL: 


--

This e-mail and any files transmitted with it are intended solely for the use 
of the individual or entity to whom they are addressed. It may contain 
unclassified but sensitive or protectively marked material and should be 
handled accordingly. Unless you are the named addressee (or authorised to 
receive it for the addressee) you may not copy or use it, or disclose it to 
anyone else. If you have received this transmission in error please notify the 
sender immediately. All traffic may be subject to recording and/or monitoring 
in accordance with relevant legislation. Any views expressed in this message 
are those of the individual sender, except where the sender specifies and with 
authority, states them to be the views of Dorset Council. Dorset Council does 
not accept service of documents by fax or other electronic means. Virus 
checking: Whilst all reasonable steps have been taken to ensure that this 
electronic communication and its attachments whether encoded, encrypted or 
otherwise supplied are free from computer viruses, Dorset Council accepts no 
liability in respect of any loss, cost, damage or expense suffered as a result 
of accessing this message or any of its attachments. For information on how 
Dorset Council processes your information, please see 
w