On Thu, Oct 18, 2012 at 6:32 AM, Daniel Polski <dan...@agelektronik.se> wrote: >> 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);
When INSERT is executed it doesn't add new row to the WAL file, it have to add new row to some database page possibly changing some other pages on the way too. These changed pages are written into WAL file. Because of this fact when INSERT statement is executed it must be executed on the latest version of the database pages. Otherwise it can create an alternative database version that can't be merged with version created by other INSERT statements. So when you execute INSERT statement, or start IMMEDIATE transaction, or convert existing read-only transaction into writing transaction SQLite have to make sure that this transaction sees the latest version of the database. But when you start read-only transaction SQLite ensures that all the way through it sees a consistent database state, and that would be the state of the database at the beginning of the transaction. So when you start executing SELECT statement you lock conn_2 into the database state that was at that moment. Then you do database changes on conn_1, so database state is changed. Then you want to execute changes in conn_2, but it can't do that because it's locked into database state which is not latest one. And it can't change the visible database state because SELECT statement is still in progress. Pavel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
