Re: [sqlite] 3.24 database table is locked
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 napisał(a): > > On 7/4/18, Wojtek Mamrak 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
On 7/4/18, Wojtek Mamrak 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
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 napisał(a): > > Yes. Yes. > czw., 5 lip 2018 o 00:28 Simon Slavin napisał(a): > > > > > > > > On 4 Jul 2018, at 11:03pm, Wojtek Mamrak 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
Yes. Yes. czw., 5 lip 2018 o 00:28 Simon Slavin napisał(a): > > > > On 4 Jul 2018, at 11:03pm, Wojtek Mamrak 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
On 4 Jul 2018, at 11:03pm, Wojtek Mamrak 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
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 napisał(a): > > That is the case, thank you! > śr., 4 lip 2018 o 18:15 Dan Kennedy 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 > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
That is the case, thank you! śr., 4 lip 2018 o 18:15 Dan Kennedy 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
