'Database table is locked' error with libgpkg and SQLite >= 3.24.0

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

'Database table is locked' error with libgpkg and SQLite >= 3.24.0

Benjamin Stadin
Hi,

I've forked libgpkg on Github to merge fixes from other repositories and update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a 'database table is locked' error for the update statements (see exact error message below). I ran the tests manually in the gpkg shell and could not reproduce the issue for the same sequence of commands. The tests are written in ruby, which could explain the different behaviour.

I tried different versions of SQLite and can confirm the RTREE tests for SQLite versions prior to 3.24.0 succeed (tested with 3.23.1 and 3.19.3). All recent versions break tests with mentioned error (tested versions include 3.24.0, 3.25.0, 3.25.3, current snapshot).

To reproduce the issue:
- Clone the repository at https://github.com/benstadin/libgpkg
- Configure with tests and RTREE:
cmake -DCMAKE_BUILD_TYPE:STRING=Release -DGPKG_TEST:BOOL=on -DSQLITE_ENABLE_RTREE=1 .
- Build
make
- Run the tests with detailed error messages:
make CTEST_OUTPUT_ON_FAILURE=1 test

Cheers
Ben

Error message:

1) CreateSpatialIndex should create working spatial index
     Failure/Error: expect("UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1").to have_result nil
       expected UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1 to have result nil but raised error 'database table is locked'
     # ./rtree_spec.rb:41:in `block (2 levels) in <top (required)>'

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

Dan Kennedy-4
On 11/21/2018 11:01 PM, Benjamin Stadin wrote:
> Hi,
>
> I've forked libgpkg on Github to merge fixes from other repositories and update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a 'database table is locked' error for the update statements (see exact error message below). I ran the tests manually in the gpkg shell and could not reproduce the issue for the same sequence of commands. The tests are written in ruby, which could explain the different behaviour.
>
> I tried different versions of SQLite and can confirm the RTREE tests for SQLite versions prior to 3.24.0 succeed (tested with 3.23.1 and 3.19.3). All recent versions break tests with mentioned error (tested versions include 3.24.0, 3.25.0, 3.25.3, current snapshot).


As of 3.24.0, rtree prevents you from writing to a table while the same
connection has an active SELECT on the same table. More detail in this
thread:

 
http://sqlite.1065341.n5.nabble.com/3-24-database-table-is-locked-td102856.html

Dan.



>
> To reproduce the issue:
> - Clone the repository at https://github.com/benstadin/libgpkg
> - Configure with tests and RTREE:
> cmake -DCMAKE_BUILD_TYPE:STRING=Release -DGPKG_TEST:BOOL=on -DSQLITE_ENABLE_RTREE=1 .
> - Build
> make
> - Run the tests with detailed error messages:
> make CTEST_OUTPUT_ON_FAILURE=1 test
>
> Cheers
> Ben
>
> Error message:
>
> 1) CreateSpatialIndex should create working spatial index
>      Failure/Error: expect("UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1").to have_result nil
>        expected UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1 to have result nil but raised error 'database table is locked'
>      # ./rtree_spec.rb:41:in `block (2 levels) in <top (required)>'
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

Richard Hipp-3
In reply to this post by Benjamin Stadin
On 11/21/18, Benjamin Stadin <[hidden email]> wrote:
> Hi,
>
> I've forked libgpkg on Github to merge fixes from other repositories and
> update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a
> 'database table is locked' error for the update statements (see exact error
> message below).

Perhaps this is a result of the following change:

     https://www.sqlite.org/src/info/d4ce66610851c825

That change is actually a bug fix.  So we cannot back it out.

Is your application trying to update the r-tree table while
simultaneously reading from the same table?  That is the source of the
problem.

The SQLITE_LOCKED_VTAB error will occur on an attempt to write to the
R-Tree.  You can perhaps discover what queries are running
concurrently against the R-Tree by querying the sqlite_stat virtual
table (https://www.sqlite.org/stmt.html) whenever you get the
SQLITE_LOCKED_VTAB error.  Perhaps:

   SELECT sql FROM sqlite_stmt WHERE busy;

It might that the concurrent queries have actually finished for
practical purposes, but the application merely failed to run
sqlite3_reset() or sqlite3_finalize() on the query and thus it
continues to hold locks that prevent writes against the R-Tree.

Perhaps you can work around this by putting an ORDER BY on the read
queries that run in parallel with the writes while making sure the
ORDER BY clause really does require a sort operation to occur.  You
can make that guarantee by adding a unary "+" operator in front of one
of the ORDER BY terms.  Perhaps:  "ORDER BY +rowid".  Adding an ORDER
BY in this way will fix the problem because it will run the entire
R-Tree query to completion, storing the results in a buffer for
sorting, prior to returning any rows.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

Benjamin Stadin
Thank you. The ruby helper class used by this library has indeed nested query executions. Modifying this class accordingly fixes the issue.

Thanks
Ben


´╗┐Am 21.11.18, 21:39 schrieb "[hidden email] im Auftrag von Richard Hipp" <[hidden email] im Auftrag von [hidden email]>:

    On 11/21/18, Benjamin Stadin <[hidden email]> wrote:
    > Hi,
    >
    > I've forked libgpkg on Github to merge fixes from other repositories and
    > update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a
    > 'database table is locked' error for the update statements (see exact error
    > message below).
   
    Perhaps this is a result of the following change:
   
         https://www.sqlite.org/src/info/d4ce66610851c825
   
    That change is actually a bug fix.  So we cannot back it out.
   
    Is your application trying to update the r-tree table while
    simultaneously reading from the same table?  That is the source of the
    problem.
   
    The SQLITE_LOCKED_VTAB error will occur on an attempt to write to the
    R-Tree.  You can perhaps discover what queries are running
    concurrently against the R-Tree by querying the sqlite_stat virtual
    table (https://www.sqlite.org/stmt.html) whenever you get the
    SQLITE_LOCKED_VTAB error.  Perhaps:
   
       SELECT sql FROM sqlite_stmt WHERE busy;
   
    It might that the concurrent queries have actually finished for
    practical purposes, but the application merely failed to run
    sqlite3_reset() or sqlite3_finalize() on the query and thus it
    continues to hold locks that prevent writes against the R-Tree.
   
    Perhaps you can work around this by putting an ORDER BY on the read
    queries that run in parallel with the writes while making sure the
    ORDER BY clause really does require a sort operation to occur.  You
    can make that guarantee by adding a unary "+" operator in front of one
    of the ORDER BY terms.  Perhaps:  "ORDER BY +rowid".  Adding an ORDER
    BY in this way will fix the problem because it will run the entire
    R-Tree query to completion, storing the results in a buffer for
    sorting, prior to returning any rows.
   
   
    --
    D. Richard Hipp
    [hidden email]
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users