> Could there be any other > consequences like unpredictable behavior and such?
Yes, it will be unpredictable and undefined behavior. I can't say exactly how SQLite will behave in such situation. What I know is it doesn't execute all select at once, it fetches row by row on each sqlite3_step call remembering where it left off before returning to your code. So imagine this: SQLite uses index on other_column to execute the select statement, it traverses index entries one by one probably skipping something because it doesn't fit your criteria. Then it finds something you need, remembers where it is and returns the row to you. Then you change other_column, and it requires to move this row in the index SQLite traversing in the select statement. Several types of behavior can follow. One is row moves forward, but SQLite remembers absolute position where it was and so it can return the same row to you once again. Second behavior is SQLite somehow moves its "position" pointer along with the index entry, so it can either loose some rows that should be returned by select statement or return some rows one again. And third type of behavior: move of index entry caused b-tree page to be deleted, but SQLite remembered absolute position, so on next step it doesn't know where it should continue its search in the index. Probably SQLite's implementation allows some other types of behavior, but in any case behavior would be "erroneous" and unpredictable, so you better avoid changing table that is currently being selected, or at very least avoid changing indexed fields if their index is used in the query. Pavel On Tue, Nov 23, 2010 at 9:50 PM, cricketfan <srtedul...@yahoo.co.in> wrote: > > Pavel, > What will happen if you had an index on the other_column for the > select/update you mentioned below? Is it just that your tree will be > unbalanced every time you change the other_column? Could there be any other > consequences like unpredictable behavior and such? > Thanks > > >> Exactly this sequence is safe. Things can go nuts in case if you have >> index on other_column and you do something like this: >> >> for value in "SELECT main_column IN mytable WHERE >> other_column='something'": >> UPDATE mytable SET other_column='foobar' WHERE main_column=value >> >> Pavel >> > > -- > View this message in context: > http://old.nabble.com/UPDATE-during-SELECT-tp30253771p30284918.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
