3.24 database table is locked

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

3.24 database table is locked

Wojtek Mamrak
Hello,

I have encountered a significant change in behavior between versions
3.24 and 3.23.
I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I
am setting threading mode option to SQLITE_CONFIG_SERIALIZED before
initializing SQLite. I have a single database connection handle which
is used by several threads which insert records into a single table.
Up to version 3.23 there were no issues with this approach, even for
multiple working threads. In version 3.24 I am getting a "database
table is locked" error. Strangely enough, many times it happens during
the first INSERT execution. The insert takes place in an insert
trigger, and records are added to an rtree_i32 table.

regards
_______________________________________________
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: 3.24 database table is locked

Dan Kennedy-4
On 07/04/2018 11:06 PM, Wojtek Mamrak wrote:

> Hello,
>
> I have encountered a significant change in behavior between versions
> 3.24 and 3.23.
> I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I
> am setting threading mode option to SQLITE_CONFIG_SERIALIZED before
> initializing SQLite. I have a single database connection handle which
> is used by several threads which insert records into a single table.
> Up to version 3.23 there were no issues with this approach, even for
> multiple working threads. In version 3.24 I am getting a "database
> table is locked" error. Strangely enough, many times it happens during
> the first INSERT execution. The insert takes place in an insert
> trigger, and records are added to an rtree_i32 table.

There was this change:

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

R-tree now prevents you from writing if the same connection currently
has an active SELECT on the same r-tree table. If you don't think this
is the case (if your app doesn't have an active SELECT) it might be a
bug. In that case can you post the database schema and the SQL statement
being executed?

Dan.




>
> regards
> _______________________________________________
> 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: 3.24 database table is locked

Wojtek Mamrak
That is the case, thank you!
śr., 4 lip 2018 o 18:15 Dan Kennedy <[hidden email]> napisał(a):

>
> On 07/04/2018 11:06 PM, Wojtek Mamrak wrote:
> > Hello,
> >
> > I have encountered a significant change in behavior between versions
> > 3.24 and 3.23.
> > I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I
> > am setting threading mode option to SQLITE_CONFIG_SERIALIZED before
> > initializing SQLite. I have a single database connection handle which
> > is used by several threads which insert records into a single table.
> > Up to version 3.23 there were no issues with this approach, even for
> > multiple working threads. In version 3.24 I am getting a "database
> > table is locked" error. Strangely enough, many times it happens during
> > the first INSERT execution. The insert takes place in an insert
> > trigger, and records are added to an rtree_i32 table.
>
> There was this change:
>
>    https://sqlite.org/src/info/d4ce66610851c825
>
> R-tree now prevents you from writing if the same connection currently
> has an active SELECT on the same r-tree table. If you don't think this
> is the case (if your app doesn't have an active SELECT) it might be a
> bug. In that case can you post the database schema and the SQL statement
> being executed?
>
> Dan.
>
>
>
>
> >
> > regards
> > _______________________________________________
> > 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
_______________________________________________
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: 3.24 database table is locked

Wojtek Mamrak
Creating a separate connection for the SELECTs does not seem to solve
the problem. What is the preferred way of tackling such a scenario? I
guarded all the calls with a mutex, but it did not help.
śr., 4 lip 2018 o 19:34 Wojtek Mamrak <[hidden email]> napisał(a):

>
> That is the case, thank you!
> śr., 4 lip 2018 o 18:15 Dan Kennedy <[hidden email]> napisał(a):
> >
> > On 07/04/2018 11:06 PM, Wojtek Mamrak wrote:
> > > Hello,
> > >
> > > I have encountered a significant change in behavior between versions
> > > 3.24 and 3.23.
> > > I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I
> > > am setting threading mode option to SQLITE_CONFIG_SERIALIZED before
> > > initializing SQLite. I have a single database connection handle which
> > > is used by several threads which insert records into a single table.
> > > Up to version 3.23 there were no issues with this approach, even for
> > > multiple working threads. In version 3.24 I am getting a "database
> > > table is locked" error. Strangely enough, many times it happens during
> > > the first INSERT execution. The insert takes place in an insert
> > > trigger, and records are added to an rtree_i32 table.
> >
> > There was this change:
> >
> >    https://sqlite.org/src/info/d4ce66610851c825
> >
> > R-tree now prevents you from writing if the same connection currently
> > has an active SELECT on the same r-tree table. If you don't think this
> > is the case (if your app doesn't have an active SELECT) it might be a
> > bug. In that case can you post the database schema and the SQL statement
> > being executed?
> >
> > Dan.
> >
> >
> >
> >
> > >
> > > regards
> > > _______________________________________________
> > > 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
_______________________________________________
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: 3.24 database table is locked

Simon Slavin-3


On 4 Jul 2018, at 11:03pm, Wojtek Mamrak <[hidden email]> wrote:

> Creating a separate connection for the SELECTs does not seem to solve
> the problem. What is the preferred way of tackling such a scenario? I
> guarded all the calls with a mutex, but it did not help.

Do you actually need to make changes while you have an active SELECT ? Are you using _reset() or _finalize() on your SELECT when you're done with it ?

Simon.
_______________________________________________
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: 3.24 database table is locked

Wojtek Mamrak
Yes. Yes.
czw., 5 lip 2018 o 00:28 Simon Slavin <[hidden email]> napisał(a):

>
>
>
> On 4 Jul 2018, at 11:03pm, Wojtek Mamrak <[hidden email]> wrote:
>
> > Creating a separate connection for the SELECTs does not seem to solve
> > the problem. What is the preferred way of tackling such a scenario? I
> > guarded all the calls with a mutex, but it did not help.
>
> Do you actually need to make changes while you have an active SELECT ? Are you using _reset() or _finalize() on your SELECT when you're done with it ?
>
> Simon.
> _______________________________________________
> 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: 3.24 database table is locked

Wojtek Mamrak
Actually, there might be some unreset SELECT while inserting new
records. I will investigate how to ensure it is not the case. Thanks
for help!
czw., 5 lip 2018 o 02:10 Wojtek Mamrak <[hidden email]> napisał(a):

>
> Yes. Yes.
> czw., 5 lip 2018 o 00:28 Simon Slavin <[hidden email]> napisał(a):
> >
> >
> >
> > On 4 Jul 2018, at 11:03pm, Wojtek Mamrak <[hidden email]> wrote:
> >
> > > Creating a separate connection for the SELECTs does not seem to solve
> > > the problem. What is the preferred way of tackling such a scenario? I
> > > guarded all the calls with a mutex, but it did not help.
> >
> > Do you actually need to make changes while you have an active SELECT ? Are you using _reset() or _finalize() on your SELECT when you're done with it ?
> >
> > Simon.
> > _______________________________________________
> > 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: 3.24 database table is locked

Richard Hipp-3
In reply to this post by Wojtek Mamrak
On 7/4/18, Wojtek Mamrak <[hidden email]> wrote:
> Creating a separate connection for the SELECTs does not seem to solve
> the problem. What is the preferred way of tackling such a scenario? I
> guarded all the calls with a mutex, but it did not help.

The change was a bug fix.  Any write to an r-tree might cause the
parts of the r-tree to be reorganized.  If that where to happen while
another thread where reading from the part being reorganized,
incorrect answers might result.

You can probably work around the problem by adding something like
"ORDER BY +rowid" to each query against the r-tree.  The "ORDER BY
+rowid" will force the query against the rtree to run to completion on
the first call too sqlite3_step(), storing the results in temporary
storage (for sorting).  Then result rows will be handed out via
subsequent sqlite3_step() calls from temporary storage, rather than
from cursors on the rtree. This approach ensures that there are no
read cursors on rtree tables when they are written.
--
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: 3.24 database table is locked

Wojtek Mamrak
I found a missing sqlite_reset call in my wrapper library, and the
solution proposed by Richard works great. So does the solution of
guarding all the sql execute calls with a mutex. I have to perform the
speed tests for the expected dataset and thread count yet. Thank you
for your help!
czw., 5 lip 2018 o 02:42 Richard Hipp <[hidden email]> napisał(a):

>
> On 7/4/18, Wojtek Mamrak <[hidden email]> wrote:
> > Creating a separate connection for the SELECTs does not seem to solve
> > the problem. What is the preferred way of tackling such a scenario? I
> > guarded all the calls with a mutex, but it did not help.
>
> The change was a bug fix.  Any write to an r-tree might cause the
> parts of the r-tree to be reorganized.  If that where to happen while
> another thread where reading from the part being reorganized,
> incorrect answers might result.
>
> You can probably work around the problem by adding something like
> "ORDER BY +rowid" to each query against the r-tree.  The "ORDER BY
> +rowid" will force the query against the rtree to run to completion on
> the first call too sqlite3_step(), storing the results in temporary
> storage (for sorting).  Then result rows will be handed out via
> subsequent sqlite3_step() calls from temporary storage, rather than
> from cursors on the rtree. This approach ensures that there are no
> read cursors on rtree tables when they are written.
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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