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

BERÉNYI Attila aberenyi at gislab.hu
Tue Mar 17 09:06:47 PDT 2015

I'm so delighted that someone is actually have the time to investigate this
in depth! Keep up the good work guys, it is really appreciated!


Just van den Broecke <just at justobjects.nl> ezt írta (időpont: 2015. márc.
17., K, 15:27):

> Hi Oliver,
> On 17-03-15 10:39, Oliver Tonnhofer wrote:
> > Hi Just,
> >
> >> On 16.03.2015, at 14:33, Just van den Broecke <just at justobjects.nl>
> wrote:
> >> 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.
> >
> >
> > Yes, I think USLEEP is only one cause. It even fails on my system if I
> put enough pressure on SQLite (500 single inserts/s are still working).
> >
> > Here is a small test script: https://gist.github.com/olt/
> fcef7445657be3b60682
> > You can change the number of concurrent writers or the number of “tiles”
> for each writer and a "process delay".
> Yes, I can even trigger the error even on the system where it worked
> before (Ubuntu 14.04, no LVM) by increasing mapproxy-seed --concurrency N.
> Did some more investigation:
> - tried same solution (global lock on DB connection) as in
> http://beets.radbox.org/blog/sqlite-nightmare.html, but realized MP uses
> MultiProcessing iso MultiThreading
> - compiled and locally installed from the sqlite3 source version
> (apt-get source libsqlite3-0). After 'configure' I see HAVE_USLEEP=1
> defined, but the problem is still there even at concurrency 1. I can
> confirm that my local libsqlite3.so is used and usleep() is called with
>   printf's in sqlite3.c. Sleeptime is at most 100000 microsecs (100ms).
> Even with --concurrency 1, I see many usleep()'s. There is no access
> other than the seeder. Also caught the error with your test script.
> - applied exception handling and a retrycounter/while loop in mbtiles.py
> store_tile() and load_tile(s)(). That suppressed the error and
> eventually resulted in successful writes/loads but still very slow and
> not elegant. (optimization: is_cached() in this case could probably
> better just do SELECT COUNT() as only the presence of the tile is checked).
> - The MapBox folks seem to have a similar issue:
> https://github.com/mapbox/mapbox-gl-native/issues/582 but are not using
> Python.
> All in all, I still think that HAVE_USLEEP being undefined is not the
> issue here, but somehow a very slow write,lock,flush or
> fsync-interaction with the LVM. mbtiles.py could be made more robust by
> catching exceptions in load_tile(s)() and some sleep/retry mechanisms,
> but if the FS is simply too slow an mbtiles/sqlite cache is suboptimal.
> If needed I could add the retry and SELECT COUNT() to the MP project via
> an issue/PR.
> Best,
> Just
> >
> >
> >
> > Regards,
> > Oliver
> >
> _______________________________________________
> MapProxy mailing list
> MapProxy at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapproxy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapproxy/attachments/20150317/9cabf818/attachment.html>

More information about the MapProxy mailing list