SQLite's Results Are Expired While Schema Is Changed !

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

SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh
I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection.
And now I find an issue that the results of SQLite C interface returned is expired while the schema of database is changed.


The following sample runs in different threads, but I force them to runsequentially.


Thread 1:
1. Conn A: Open, PRAGMA journal_mode=WAL
Thread 2:
2.ConnB: Open, PRAGMA journal_mode=WAL
Thread 1:
3.ConnA: CREATE TABLE sample (i INTEGER);
Thread 2:
4.ConnB: PRAGMA table_info('sample')


Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
Then, Conn A creates a table with Conn A.
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
The same thing could happen if I change the step 4 to `sqlite3_table_column_metadata` or some other interfaces.


I do know the reason should be the expired in-memory-schema. But I find no docs about which interface will or will not update the schema and what should I do while I call a non-update-schema interface ?
_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Clemens Ladisch
sanhua.zh wrote:

> 1. Conn A: Open, PRAGMA journal_mode=WAL
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> 4.ConnB: PRAGMA table_info('sample')
>
> Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
>
> I do know the reason should be the expired in-memory-schema.

No, SQLite automatically detects schema changes.

It's likely that the second connection started its transaction before
the first connection committed its own, so it still sees the old state
of the database.


Regards,
Clemens
_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Jay Kreibich

On Aug 18, 2017, at 5:33 AM, Clemens Ladisch <[hidden email]> wrote:

> sanhua.zh wrote:
>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>> 4.ConnB: PRAGMA table_info('sample')
>>
>> Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
>> Then, Conn A creates a table with Conn A.
>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
>>
>> I do know the reason should be the expired in-memory-schema.
>
> No, SQLite automatically detects schema changes.

…but only automatically re-prepares the expired statements if the statement
was originally prepared using sqlite3_prepare*_v2 or _v3.


> It's likely that the second connection started its transaction before
> the first connection committed its own, so it still sees the old state
> of the database.

Unlike most RDBMS environments, SQLite handles DDL as part of
normal transactions.  I don’t think this situation would be a problem,
and would be handled by the normal locking mechanisms.

  -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson



_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Jay Kreibich
In reply to this post by sanhua.zh

On Aug 18, 2017, at 4:04 AM, sanhua.zh <[hidden email]> wrote:

> I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection.
> And now I find an issue that the results of SQLite C interface returned is expired while the schema of database is changed.
>
>
> The following sample runs in different threads, but I force them to runsequentially.
>
>
> Thread 1:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> Thread 2:
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> Thread 1:
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> Thread 2:
> 4.ConnB: PRAGMA table_info('sample')
>
>
> Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
> The same thing could happen if I change the step 4 to `sqlite3_table_column_metadata` or some other interfaces.
>
>
> I do know the reason should be the expired in-memory-schema. But I find no docs about which interface will or will not update the schema and what should I do while I call a non-update-schema interface ?


See the bottom of the sqlite3_prepare*() docs:

https://www.sqlite.org/c3ref/prepare.html

And the SQLITE_SCHEMA docs:

https://www.sqlite.org/rescode.html#schema



As the docs say, make sure you’re using sqlite3_prepare*_v2() or _v3().  If a statement is prepared with these newer versions, it will handle most expiration situations automatically by re-preparing the statement.

Generally speaking, if you do get an SQLITE_SCHEMA error, you need to rollback the current transaction, re-prepare the statements, and try again.

   -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson



_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Clemens Ladisch
In reply to this post by Jay Kreibich
Jay Kreibich wrote:

> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch <[hidden email]> wrote:
>> sanhua.zh wrote:
>>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>>> 4.ConnB: PRAGMA table_info('sample')
>>>
>>> Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
>>> Then, Conn A creates a table with Conn A.
>>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
>>>
>>> I do know the reason should be the expired in-memory-schema.
>>
>> No, SQLite automatically detects schema changes.
>
> …but only automatically re-prepares the expired statements if the statement
> was originally prepared using sqlite3_prepare*_v2 or _v3.

The word "expired" in the OP is misleading; this problem has nothing to do
with schema expiration (that would result in an SQLITE_SCHEMA error).


Regards,
Clemens
_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Jay Kreibich

On Aug 18, 2017, at 7:37 AM, Clemens Ladisch <[hidden email]> wrote:

> Jay Kreibich wrote:
>> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch <[hidden email]> wrote:
>>> sanhua.zh wrote:
>>>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>>>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>>>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>>>> 4.ConnB: PRAGMA table_info('sample')
>>>>
>>>> Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.
>>>> Then, Conn A creates a table with Conn A.
>>>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.
>>>>
>>>> I do know the reason should be the expired in-memory-schema.
>>>
>>> No, SQLite automatically detects schema changes.
>>
>> …but only automatically re-prepares the expired statements if the statement
>> was originally prepared using sqlite3_prepare*_v2 or _v3.
>
> The word "expired" in the OP is misleading; this problem has nothing to do
> with schema expiration (that would result in an SQLITE_SCHEMA error).

Ah…. OK yes.

 -j


--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson



_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Jens Alfke-2
In reply to this post by sanhua.zh

> On Aug 18, 2017, at 2:04 AM, sanhua.zh <[hidden email]> wrote:
>
> I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection.

Actually, "multi-threaded mode" usually means using a _single_ connection on multiple threads.

What you're doing — a separate connection for each thread — is effectively* the same as running multiple single-threaded processes, i.e. it doesn't involve any concurrency within SQLite.

—Jens

* There are slight differences, because of some global state in the SQLite library (like logging callbacks), but that's irrelevant to what's going on here.
_______________________________________________
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: SQLite's Results Are Expired While Schema Is Changed !

Simon Slavin-3


On 18 Aug 2017, at 5:39pm, Jens Alfke <[hidden email]> wrote:

> Actually, "multi-threaded mode" usually means using a _single_ connection on multiple threads.
>
> What you're doing — a separate connection for each thread — is effectively* the same as running multiple single-threaded processes, i.e. it doesn't involve any concurrency within SQLite.

We need a grid, with number of connections along the top and number of threads down the side.  Might be useful in the documentation.

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: SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh
In reply to this post by sanhua.zh
Firstly, I use `PRAGMA table_info('sample')` in my sample case, which means that it also calls `sqlite3_prepare_v2` but do not re-prepare. Maybe it does not contain the specific OP checking the schema.


Secondly, it's hard to know when the schema is changed in multi-conns implementation. So as telling them to re-prepare.


So, As your word "comthis situation would be a problem" said, is it that I should not use the non-update-schema-operation in multi-conns implementation ?
If so, who or which doc can tell me that which SQL will or will not update the schema ?


Original Message
Sender:Jay [hidden email]
Recipient:SQLite mailing [hidden email]
Date:Friday, Aug 18, 2017 19:46
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !


On Aug 18, 2017, at 4:04 AM, sanhua.zh [hidden email] wrote:  I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection.  And now I find an issue that the results of SQLite C interface returned is expired while the schema of database is changed.    The following sample runs in different threads, but I force them to runsequentially.    Thread 1:  1. Conn A: Open, PRAGMA journal_mode=WAL  Thread 2:  2.ConnB: Open, PRAGMA journal_mode=WAL  Thread 1:  3.ConnA: CREATE TABLE sample (i INTEGER);  Thread 2:  4.ConnB: PRAGMA table_info('sample')    Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.  Then, Conn A creates a table with Conn A.  Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.  The same thing could happen if I change the step 4 to `sqlite3_table_column_metadata` or some other interfaces.    I do know the reason should be the expired in-memory-schema. But I find no docs about which interface will or will not update the schema and what should I do while I call a non-update-schema interface ? See the bottom of the sqlite3_prepare*() docs: https://www.sqlite.org/c3ref/prepare.html And the SQLITE_SCHEMA docs: https://www.sqlite.org/rescode.html#schema As the docs say, make sure you’re using sqlite3_prepare*_v2() or _v3(). If a statement is prepared with these newer versions, it will handle most expiration situations automatically by re-preparing the statement. Generally speaking, if you do get an SQLITE_SCHEMA error, you need to rollback the current transaction, re-prepare the statements, and try again. -j -- Jay A. Kreibich  J A Y @ K R E I B I.C H  "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ 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: SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh
In reply to this post by sanhua.zh
Hello Jay, here is information update.


I find 2 new things.
1. This issue happens even two conns running in same thread.
2. And it is more important ! This issue only happens in WAL mode !


As I know, databases with journal mode use a file change counter to update schema while databases with WAL mode use wal-index to do this.
I'm using macOS with the system builtin sqlite framwork with 3.8.10.2. I think it should be a bug in wal-index.


Here is the very simple code to reproduce this problem.


```
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


sqlite3* handle1;
rc = sqlite3_open(path, handle1);
assert(rc==0);
//  rc = sqlite3_exec(handle1, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
//  assert(rc==0);


sqlite3* handle2;
rc = sqlite3_open(path, handle2);
assert(rc==0);
//  rc = sqlite3_exec(handle2, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
//  assert(rc==0);


rc = sqlite3_exec(handle1, "CREATE TABLE test1 (i INTEGER)", NULL, NULL, NULL);
assert(rc==0);
//The sql below will return an error with 'no such table: test1' in WAL, but succeeds in journal mode.
rc = sqlite3_exec(handle2, "SELECT * FROM test1", NULL, NULL, NULL);
assert(rc==0);
```




Original Message
Sender:Jay [hidden email]
Recipient:SQLite mailing [hidden email]
Date:Friday, Aug 18, 2017 19:46
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !


On Aug 18, 2017, at 4:04 AM, sanhua.zh [hidden email] wrote:  I am using SQLite in multi-thread mode, which means that different threads using different SQLite connection.  And now I find an issue that the results of SQLite C interface returned is expired while the schema of database is changed.    The following sample runs in different threads, but I force them to runsequentially.    Thread 1:  1. Conn A: Open, PRAGMA journal_mode=WAL  Thread 2:  2.ConnB: Open, PRAGMA journal_mode=WAL  Thread 1:  3.ConnA: CREATE TABLE sample (i INTEGER);  Thread 2:  4.ConnB: PRAGMA table_info('sample')    Firstly, both thread 1 and 2 do initialization for their own conn, which is to read to schema into memory.  Then, Conn A creates a table with Conn A.  Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it returns nothing.  The same thing could happen if I change the step 4 to `sqlite3_table_column_metadata` or some other interfaces.    I do know the reason should be the expired in-memory-schema. But I find no docs about which interface will or will not update the schema and what should I do while I call a non-update-schema interface ? See the bottom of the sqlite3_prepare*() docs: https://www.sqlite.org/c3ref/prepare.html And the SQLITE_SCHEMA docs: https://www.sqlite.org/rescode.html#schema As the docs say, make sure you’re using sqlite3_prepare*_v2() or _v3(). If a statement is prepared with these newer versions, it will handle most expiration situations automatically by re-preparing the statement. Generally speaking, if you do get an SQLITE_SCHEMA error, you need to rollback the current transaction, re-prepare the statements, and try again. -j -- Jay A. Kreibich  J A Y @ K R E I B I.C H  "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ 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: SQLite's Results Are Expired While Schema Is Changed !

Simon Slavin-3
In reply to this post by sanhua.zh


On 21 Aug 2017, at 3:22am, sanhua.zh <[hidden email]> wrote:

> If so, who or which doc can tell me that which SQL will or will not update the schema ?

Commands with CREATE, DROP, or ALTER update schema.

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: SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh
In reply to this post by sanhua.zh
And which commands will check whether the schema is expired.
As far as I know, SELECT command will but PRAGMA table_info will not do this.


Original Message
Sender:Simon [hidden email]
Recipient:SQLite mailing [hidden email]
Date:Monday, Aug 21, 2017 11:47
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !


On 21 Aug 2017, at 3:22am, sanhua.zh [hidden email] wrote:  If so, who or which doc can tell me that which SQL will or will not update the schema ? Commands with CREATE, DROP, or ALTER update schema. 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