Sporadic error with multi-threaded overview generation

[matsamentet]

What is the bug?

Running BuildOverviews on a GeoPackage dataset with config GDAL_NUM_THREADS=ALL_CPUS and bilinear resampling results in sporadic errors. The errors seem to happen around 50% of the time. Simpler resampling methods such as average and nearest does not appear to cause the error.

Steps to reproduce the issue

Running a script to repeat the BuildOverviews() function on the same source
MassiveGeopackage_output.zip
file multiple times. The files are stored on a spinning hard drive.

from osgeo import gdal
import os
import shutil

srcfile = r'F:\05_Annet\2024-06-19-gdaladdo-bug\data\MassiveGeopackage_output.gpkg'
copiedfile = r'F:\05_Annet\2024-06-19-gdaladdo-bug\out\test-overviews.gpkg'

for i in range(20):
    print(f'Attempt {i}')
    
    shutil.copy(srcfile,copiedfile)
    gdal.SetConfigOption('GDAL_NUM_THREADS','ALL_CPUS')
    
    gpkg_ds = gdal.OpenEx(copiedfile,1)
    gpkg_ds.BuildOverviews(resampling='bilinear', overviewlist=[2,4])
    gpkg_ds = None

    os.unlink(copiedfile)

This results in the following output:

Attempt 1
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: F:\05_Annet\2024-06-19-gdaladdo-bug\out\test-overviews.gpkg - zoom_level=1, band 1: IReadBlock failed at X offset 0, Y offset 0
Attempt 2
Attempt 3
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: F:\05_Annet\2024-06-19-gdaladdo-bug\out\test-overviews.gpkg - zoom_level=1, band 1: IReadBlock failed at X offset 0, Y offset 0
Attempt 4
Attempt 5
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: F:\05_Annet\2024-06-19-gdaladdo-bug\out\test-overviews.gpkg - zoom_level=1, band 1: IReadBlock failed at X offset 0, Y offset 0
Attempt 6
Attempt 7
Attempt 8
Attempt 9
Attempt 10
Attempt 11
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: F:\05_Annet\2024-06-19-gdaladdo-bug\out\test-overviews.gpkg - zoom_level=1, band 1: IReadBlock failed at X offset 0, Y offset 0
Attempt 12
Attempt 13
Attempt 14
Attempt 15
Attempt 16
Attempt 17

Versions and provenance

Windows 11
Python 3.12
GDAL 3.8.2 wheel from https://github.com/cgohlke/geospatial-wheels

Additional context

No response

[jratike80]

Of course there should be no errors of crashes, but I wonder how much sense there is in creating overviews in multiple threads. GeoPackage is an SQLite database and only one process at a time can write. But maybe it is slower to create the overviews than to save them into the database. Fortunately that should be rather easy to test.

[rouault]

but I wonder how much sense there is in creating overviews in multiple threads.

Multithreading in overview generation only affects the computation part (well for the GeoTIFF driver, this can also cause multi-threaded writes within the driver). This can help a bit depending on the resampling kernel. The issue here was that one of those computation worker threads accidentally queried the NBITS metadata item on the overview band, which on the first call can cause a SQLite request, wheras the I/O thread could potentially read or write GeoPackage blobs at the same time, hence a concurrent use of the same SQLite handle. Fixed per #10246