[gdal-dev] Getting database is locked error when writing to GeoPackage database.

[jkellio2]

I'm using the OGR/GDAL C# managaged API to write to a GeoPackage database and
I keep running into a "database is locked" error when I try to sync the
database to disk.  After much experimentation, this appears to only happen
if I first open the GeoPackage to do a read, close the db (Dispose the
DataSource), reopen db and attempt to write.  Web searches indicate that
this error occurs if the GDAL and SQLite objects from the read are not
disposed before attempting the write.  However, I think I'm disposing all
the managed objects properly so I'm wondering if there is a call that I'm
missing.  Any help would be greatly appreciated.  I've included a sample
Console app that shows the error I'm getting. It attempts to create a new
layer but I get same error if I try updating an existing layer.  I'm using
GeoPackage v1.2, GDAL v2.3, and SQLite v3.21.0 on Windows.


using System;
using System.Collections.Generic;
using OSGeo.OGR;
namespace GeoPackageWriteTest
{
  class GeoPackageTest
  {
    public static void usage()
    {
      Console.WriteLine( "usage (to create a new layer):" );
      Console.WriteLine( "gpkgWriteTest {gpkg filename} {layername}" );
      System.Environment.Exit( 0 );
    }
    static void Main( string[] args )
    {
      if ( args.Length < 2 )
        usage();

      Ogr.RegisterAll();

      // simulate an object opening a geopackage db to do a simple read
      Console.WriteLine( "Opening database readonly: " + args[0] );
      DataSource dataSource1 = Ogr.OpenShared( args[0], 0 ); // open
readonly
      if ( dataSource1 == null )
        throw new ArgumentException( string.Format( "Error opening
GeoPackage database: {0}", args[0] ) );
      using ( dataSource1 )
      {
        Driver driver = dataSource1.GetDriver();
        if ( driver == null )
          throw new ArgumentException( "Error getting GDAL driver!" );
        Console.WriteLine( "Using driver: " + driver.name );

        string pfile = GetParameterTableName( dataSource1 );
        Console.WriteLine( "Parameter table: " + pfile );
      } // DataSource gets disposed and geopackage is closed
      dataSource1 = null;

      // make sure all Disposed objects are garbage collected
      // (just trying to see if it makes a difference, it doesn't)
      GC.Collect( GC.MaxGeneration, GCCollectionMode.Forced, true, true );
      GC.WaitForPendingFinalizers();

      // simulate another object reopening a geopackage db to do a simple
write
      Console.WriteLine( "Opening database readwrite: " + args[0] );
      DataSource dataSource2 = Ogr.OpenShared( args[0], 1 );  // reopen
readwrite
      if ( dataSource2 == null )
        throw new ArgumentException( string.Format( "Error opening
GeoPackage database: {0}", args[0] ) );
      using ( dataSource2 )
      {
        List<string> options = new List<string>();
        options.Add( "SPATIAL_INDEX=YES" );

        Console.WriteLine( "Creating layer: " + args[1] );
        Layer tableLayer = dataSource2.CreateLayer( args[1], null,
wkbGeometryType.wkbNone, options.ToArray() );
        if ( tableLayer == null )
          throw new ArgumentException( string.Format( "Error creating layer:
{0}", args[2] ) );

        int rc = Ogr.OGRERR_NONE;
        int approx_ok = 1;
        int fieldCount = 3;
        for ( int i = 0; i < fieldCount; i++ )
        {
          //FieldDefn fieldDefn = GetFieldDefn( args[n], args[n + 1] );
          string fname = string.Format( "TestField{0}", i );
          FieldDefn fieldDefn = GetFieldDefn( fname, "int32" );
          rc = tableLayer.CreateField( fieldDefn, approx_ok );
          if ( rc != Ogr.OGRERR_NONE )
            throw new ArgumentException( string.Format( "Error creating
field: {0}", fname ) );
        }
        // write changes to disk
        Console.WriteLine( "Writing changes to disk..." );
        dataSource2.FlushCache(); // this throws database locked error
      }
    }
    static string GetParameterTableName( DataSource ogrDataSource )
    {
      try
      {
        string s = string.Empty;
        string sql = "select * from sqlite_master where type='table' and
name like '%Parameter%'";
        OSGeo.OGR.Layer layer = ogrDataSource.ExecuteSQL( sql, null,
"SQLITE" );
        using ( layer )
        {
          layer.ResetReading();
          OSGeo.OGR.Feature fea = layer.GetNextFeature();
          if ( fea != null )
          {
            s = fea.GetFieldAsString( "name" );
          }
        }
        return s;
      }
      catch ( Exception )
      {
        return string.Empty;
      }
    }
    private static FieldDefn GetFieldDefn( string fldName, string fldType )
    {
      //OSGeo.OGR.FieldType fieldType = GetOGRFieldType( fldType );
      OSGeo.OGR.FieldType fieldType = FieldType.OFTInteger;
      OSGeo.OGR.FieldSubType subType = FieldSubType.OFSTNone;

      FieldDefn fieldDefn = new FieldDefn( fldName, fieldType );
      fieldDefn.SetSubType( subType );
      return fieldDefn;
    }
  }
}




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html