On 14 Jun 2013, at 11:29pm, DL <jhn...@yahoo.com> wrote: > UPDATE T set C1 = calculation(C2) where C1 is NULL > If table is large, this update may take many seconds or even minutes. > During this time all other queries on this table fail with "database is > locked" > after connection timeout expires (currently my timeout is 5 seconds).
Do you have an index on T(C1) ? That should dramatically reduce the search time. > I would like to stop this update query after, say, 3 seconds, > then restart it. Hopefully, after several restarts entire table will be > updated. > Another option is to stop this update query before making any other request > (this will require inter-process cooperation, but it may be doable). Well, you could increase your timeout: <http://www.sqlite.org/pragma.html#pragma_busy_timeout> <http://www.sqlite.org/c3ref/busy_timeout.html> which will fix the problem one way. Another way would be to use the LIMIT clause on UPDATE, if it’s available to you: <http://www.sqlite.org/lang_update.html> Do an UPDATE LIMIT 10000, then check the total_changes() function <http://www.sqlite.org/lang_corefunc.html#total_changes> and if it’s not zero pause a while for your other processes to do their thing, then do another UPDATE LIMIT 10000. If compiling your own SQLite is a problem for you, step through your big table a section at a time: UPDATE T SET C1 = calculation(C2) WHERE C1 IS NULL AND rowid < 10000 then pause for a while, then do it again but this time up to row 20000, etc.. Simon. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
