[MapProxy] FYI: Possible root cause of 'sqlite3.OperationalError: database is locked'

[Just van den Broecke]

I begin to suspect that USLEEP is not the cause here but the underlying 
filesystem. I've found other causes of this error when an sqlite db is 
on a networked filesystem like NFS, CIFS/Samba. Several refs found and 
http://sqlite.org/faq.html#q5. Not a good idea anyway, but it hinted me 
to look into that direction.

In my case I use Ubuntu VMs via QEMU/KVM with LVM as storage manager on 
two RAID-1 disks. Could LVM be the culprit? I tried 3 different VMs, all 
running Ubuntu 14.04.2 with exactly the same sqlite3 shared .so libs 
(date+size) and all on Ext4 filesystems without any custom installs:

- no errors (and fast writes) on VM without underlying LVM storage
- always errors (and slow writes) on VMs with LVM
- no errors when using RAM disk on same VM that showed failures with LVM

Ok, the RAM disk may be unrealistic, but the VM without LVM not giving 
errors suspects that LVM does not handle locks appropriately (?). 
Seeding with mbtiles was slow on LVM-VMs (File-based caches fast). Tried 
several options to mapproxy-seed like concurrency 1 and use-cache-lock 
but to no avail.

Still no hard evidence. LVM is quite common, cannot find similar issues 
for LVM+sqlite3. I have no other file or db-issues with LVM. LVM has 
quite some locking config options though...

Thanks for any hints.

best,

Just



On 15-03-15 18:17, Just van den Broecke wrote:
> Oops, forget my last mail: also observed on Ubuntu 14.04 :-(.
> (my reseed yaml config was still seeding a file cache).
>
> The package sqlite3 seems not to be involved. I can even uninstall the
> package. The library /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
> provided by package libsqlite3-0 seems to be used from within Python.
> Digging further....
>
> best,
>
> Just
>
> On 15-03-15 17:07, Just van den Broecke wrote:
>> Hi,
>>
>> I confirm this behaviour for MapProxy 1.7.1 on Ubuntu (with UbuntuGIS
>> unstable PPA) using the standard Ubuntu package for sqlite3:
>>
>> - on Ubuntu 13.10 with "sqlite3 amd64 3.7.17-1ubuntu1", the
>> 'sqlite3.OperationalError: database is locked'-error appeared.
>>
>> - after upgrading to Ubuntu 14.04.2 with "sqlite3 amd64 3.8.2-1ubuntu2",
>> the error no longer occurred.
>>
>> Though the issue itself seems not to be version-dependent, but on the
>> compiler-directive HAVE_USLEEP (which must be 1). So that flag may have
>> been set for Ubuntu 14.04, which is as a better choice (LTS) anyway.
>> Maybe of use for reproducing via e.g. Vagrant.
>>
>> best,
>>
>> Just
>>
>> On 10-01-15 18:36, Oliver Tonnhofer wrote:
>>> Hi,
>>>
>>> a few users reported 'sqlite3.OperationalError: database is locked’
>>> during seeding or moderate load when using SQLite or MBTiles backends.
>>> I could never reproduce and debug this on my systems, but I think that
>>> I found the root cause.
>>>
>>> On some systems SQLite is not compiled with USLEEP support. Without
>>> USLEEP, SQLite will sleep a whole second every time another
>>> thread/process is reading or writing to the same SQLite database.
>>>
>>> Here is a detailed description of the issue:
>>> http://beets.radbox.org/blog/sqlite-nightmare.html
>>>
>>> You should try to upgrade your system or the SQLite version used by
>>> Python if you see these lock errors.
>>>
>>>
>>> Regards,
>>> Oliver
>>>
>>
>>
>> _______________________________________________
>> MapProxy mailing list
>> MapProxy at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapproxy
>
>
>
>
>
>
>
> _______________________________________________
> MapProxy mailing list
> MapProxy at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapproxy