The SELECT statement, including the _prepare() stage and all the _step()s until
you've reached the last row, and then the _finalize(), is all one process.
They're all part of the statement and you can assume that the database is still
locked until you do a _finalize().
If you are using the results of a SELECT to figure out a bunch of other
instructions like INSERT or DELETE you can do it two ways:
A) Quicky save the results of stepping into an array. Once the SELECT is
finalized, look through the array and figure out what you want to do about it.
B) Generate the INSERT/DELETE commands while stepping but keep them somewhere,
either as a data array or by accumulating the text of the SQL commands in a
string, rather than executing them immediately. Once you're finished stepping,
execute the commands. (You may choose to use _exec to execute them all in one
go.)
Thank you for the clarification, but I still misunderstand the
documentation some way.
In the documentation about WAL mode it says:
"Writers merely append new content to the end of the WAL file. Because
writers do nothing that would interfere with the actions of readers,
writers and readers can run at the same time. However, since there is
only one WAL file, there can only be one writer at a time."
Maybe the magic words I don't fully understand are what's written later:
"A checkpoint operation takes content from the WAL file and transfers it
back into the original database file. A checkpoint can run concurrently
with readers, however the checkpoint must stop when it reaches a page in
the WAL that is past the read mark of any current reader. The checkpoint
has to stop at that point because otherwise it might overwrite part of
the database file that the reader is actively using. The checkpoint
remembers (in the wal-index) how far it got and will resume transferring
content from the WAL to the database from where it left off on the next
invocation."
I logically do understand that there can't be 2 writers updating the
database at the same time, but I don't understand why the second insert
statement in the example below won't work without finalizing the SELECT
query?
sqlite3* conn_1;
sqlite3* conn_2;
//(..opening db files and so on)
sqlite3_stmt* pVM_1;
sqlite3_stmt* pVM_2;
sqlite3_prepare(conn_1, "SELECT * FROM test_table_2;", -1, &pVM_1,
&szTail); //sets WAL end mark for pVM_1?
sqlite3_prepare(conn_2, "SELECT * FROM test_table_2;", -1, &pVM_2,
&szTail); //sets WAL end mark for pVM_2?
nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1,
set new read mark?
nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2,
set new read mark?
//statements below will add content to the end of the WAL file?
nRet = sqlite3_exec(conn_1, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, &szError);
nRet = sqlite3_exec(conn_1, "INSERT INTO test_table_1 VALUES(1, 1);", 0,
0, &szError);
nRet = sqlite3_exec(conn_1, "COMMIT;", 0, 0, &szError);
nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1,
set new read mark?
nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2,
set new read mark?
// sqlite3_finalize(pVM_1);
// sqlite3_finalize(pVM_2);
//The execution below will fail with SQLITE_BUSY if the SELECT statement
pVM_2 isn't finalized
//(Why won't it append new data in the end of the WAL file just like the
successful insert above?)
nRet = sqlite3_exec(conn_2, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, &szError);
nRet = sqlite3_exec(conn_2, "INSERT INTO test_table_1 VALUES(1, 1);", 0,
0, &szError);
nRet = sqlite3_exec(conn_2, "COMMIT;", 0, 0, &szError);
Best Regards,
Daniel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
