Schema updates across threads in WAL & multithread mode

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

Schema updates across threads in WAL & multithread mode

Ben Asher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Schema updates across threads in WAL & multithread mode

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Schema updates across threads in WAL & multithread mode

Simon Slavin-3
In reply to this post by Ben Asher
On 16 Aug 2019, at 7:32pm, Ben Asher <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Schema updates across threads in WAL & multithread mode

Richard Hipp-3
In reply to this post by Ben Asher
On 8/16/19, Ben Asher <[hidden email]> 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
[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: Schema updates across threads in WAL & multithread mode

Ben Asher
In reply to this post by Ben Asher
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Schema updates across threads in WAL & multithread mode

Hick Gunter
As already stated, this looks like you have at least one transaction underways. Your schema change will become visible only after

1) they are committed on ther "writer" connection AND
2) a new transaction is started on the "reader" connection

If your "readers" are failing to reset or finalize any of their statements, the corresponding connection will have an active transaction that you may be unaware of and that prevents the schema change being seen.

For diagnostic purposes ONLY, try opening with shared cache enabled and execute pragma read_uncommitted. If the problem goes away, then you are keeping transactions open longer than you think.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Ben Asher
Gesendet: Freitag, 16. August 2019 21:49
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] 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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users