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);
