Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi D. Richard Hipp. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> > >When a database connection has a read transaction open, it continues >to see a snapshot of the database as it existed when the read >transaction was first opened. Outside changes to the database, >including schema changes, are invisible to the connection holding the >transaction. This is the "I" in "ACID". > >As soon as you release the read transaction and start another, all >changes will immediately become visible. > >If you are not deliberately holding a read transaction open, perhaps >you are doing so accidentally by failing to sqlite3_reset() or >sqlite3_finalize() a prepared statement. You can perhaps figure out >which statement that is by running: > > SELECT sql FROM sqlite_stmt WHERE busy; That is very interesting. We definitely don't expect a read transaction to be open at that point. This is super helpful. I'll check on this next week. Thank you! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi Simon. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had written: >Can I ask the maximum number of columns you expect to exist in that table ? I'm working up to trying to convince you to add a row to something instead, but I want to make sure you're doing what I think you're doing. > >Other people may be able to answer your question. It's a small number of columns– less than 10. The table already has data, and we added the column with a default value. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Hi José. Sorry for the late reply. I had some email settings wrong somewhere, and I didn't realize I had a reply until I remembered this and checked the archives months later. You had asked: > Are you using BEGIN and END before and after the schema update? Yes that's correct. We are doing the schema updates inside of an explicit transaction. Thanks! Ben On Fri, Aug 16, 2019 at 12:49 PM Ben Asher wrote: > To clarify, we add a column on our writer connection, and then "SELECT * > FROM table" on the reader connection does not include the column that was > added. > > Ben > > On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > >> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a >> schema update (adding a column to a table) on our writer connection, but >> then the schema update isn't immediately available on the read-only >> connections that we use on other threads, which causes a crash in our >> application (app expects the column to exist at that point). I've verified >> that the column does indeed get added, and everything works fine after >> restarting the application (i.e. all connections loaded fresh pickup the >> schema update). >> >> Is there something we need to do proactively to ensure that schema update >> appears immediately from other threads? >> >> Some notes about our setup: >> >> sqlite 3.27.2 >> Using multithread mode (SQLITE_OPEN_NOMUTEX) >> Using WAL mode >> >> Thanks! >> >> Ben >> > > > -- > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
To clarify, we add a column on our writer connection, and then "SELECT * FROM table" on the reader connection does not include the column that was added. Ben On Fri, Aug 16, 2019 at 11:32 AM Ben Asher wrote: > Hi folks! We're running (sqlite 3.27.2) into an issue where we make a > schema update (adding a column to a table) on our writer connection, but > then the schema update isn't immediately available on the read-only > connections that we use on other threads, which causes a crash in our > application (app expects the column to exist at that point). I've verified > that the column does indeed get added, and everything works fine after > restarting the application (i.e. all connections loaded fresh pickup the > schema update). > > Is there something we need to do proactively to ensure that schema update > appears immediately from other threads? > > Some notes about our setup: > > sqlite 3.27.2 > Using multithread mode (SQLITE_OPEN_NOMUTEX) > Using WAL mode > > Thanks! > > Ben > -- Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
On 8/16/19, Ben Asher wrote: > > Is there something we need to do proactively to ensure that schema update > appears immediately from other threads? > When a database connection has a read transaction open, it continues to see a snapshot of the database as it existed when the read transaction was first opened. Outside changes to the database, including schema changes, are invisible to the connection holding the transaction. This is the "I" in "ACID". As soon as you release the read transaction and start another, all changes will immediately become visible. If you are not deliberately holding a read transaction open, perhaps you are doing so accidentally by failing to sqlite3_reset() or sqlite3_finalize() a prepared statement. You can perhaps figure out which statement that is by running: SELECT sql FROM sqlite_stmt WHERE busy; -- 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] Schema updates across threads in WAL & multithread mode
On 16 Aug 2019, at 7:32pm, Ben Asher wrote: > we make a > schema update (adding a column to a table) on our writer connection Can I ask the maximum number of columns you expect to exist in that table ? I'm working up to trying to convince you to add a row to something instead, but I want to make sure you're doing what I think you're doing. Other people may be able to answer your question. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema updates across threads in WAL & multithread mode
Ben Asher, on Friday, August 16, 2019 02:32 PM, wrote... > > Hi folks! We're running (sqlite 3.27.2) into an issue where we make a > schema update (adding a column to a table) on our writer connection, but > then the schema update isn't immediately available on the read-only > connections that we use on other threads, which causes a crash in our > application (app expects the column to exist at that point). I've verified > that the column does indeed get added, and everything works fine after > restarting the application (i.e. all connections loaded fresh pickup the > schema update). > > Is there something we need to do proactively to ensure that schema update > appears immediately from other threads? Are you using BEGIN and END before and after the schema update? josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema updates across threads in WAL & multithread mode
Hi folks! We're running (sqlite 3.27.2) into an issue where we make a schema update (adding a column to a table) on our writer connection, but then the schema update isn't immediately available on the read-only connections that we use on other threads, which causes a crash in our application (app expects the column to exist at that point). I've verified that the column does indeed get added, and everything works fine after restarting the application (i.e. all connections loaded fresh pickup the schema update). Is there something we need to do proactively to ensure that schema update appears immediately from other threads? Some notes about our setup: sqlite 3.27.2 Using multithread mode (SQLITE_OPEN_NOMUTEX) Using WAL mode Thanks! Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
