Title: Sqlite3: How to interrupt a long running update without roll back?
Tags: sqlite
Question: I have a long running multirow update such as:
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).
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).
But I cannot find a way to stop update query without rolling back
all previously updated records.
I tried calling interrupt and returning non-0 from progress_handler.
Both these approaches abort the update command
and roll back all the changes.
So, it appears that sqlite treats this update as a transaction,
which does not make much sense in this case because all rows are independent.
But I cannot start a new transaction for each row, can I?
If interrupt and progress_handler cannot help me, what else I can do?
I also tried UPDATE with LIMIT and also WHERE custom_condition(C1).
These approaches do allow me to terminate update earlier,
but they are significantly slower than regular update
and they cannot terminate the query at specific time
(before another connection timeout expires).
Any other ideas?
This multirow update is such a common operation
that, I hope, other people have a good solution for it.
Comment: Yes, read requests can obtain old data, which is NULL. And yes, interleaving and several passes are the desired behavior (as I wrote 'Hopefully, after several restarts entire table will be updated. ') Currently the only solution I see is to copy entire DB, update it, then move it back, but it is ugly.

Comment: Multi-row update concurrent with reads by other processes is common enough in multi-user databases but SQlite is not a bona-fide multi-user database. It's not clear whether you want the other blocked processes to have the freshest data or are willing to give them stale data, interleaving their requests with update that is performed in several passes with some sort of batch control mechanism.


Here is another answer: So, it appears that sqlite treats this update as a transaction, which does not make much sense in this case because all rows are independent.
No, that actually makes perfect sense, because you're not executing multiple, independent updates. You're executing a single update statement. The fine manual says
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically start a transaction if one is
not already in effect. Automatically started transactions are
committed when the last query finishes.
If you can determine the range of keys involved, you can execute multiple update statements. For example, if a key is an integer, and you determine the range to be from 1 to 1,000,000, you can write code to execute this series of updates.
begin transaction;
UPDATE T set C1 = calculation(C2)
where C1 is NULL and your_key between 1 and 100000;
commit;
begin transaction;
UPDATE T set C1 = calculation(C2)
where C1 is NULL and your_key between 100001 and 200000;
commit;
Other possibilities . . .
You can sleep for a bit between transactions to give other queries a chance to execute.
You can also time execution using application code, and calculate a best guess at range values that will avoid timeouts and still give good performance.
You can select the keys for the rows that will be updated, and use their values to optimize the range of keys.
In my experience, it's unusual to treat updates this way, but it sounds like it fits your application.
But I cannot start a new transaction for each row, can I?
Well, you can, but it's probably not going to help. It's essentially the same as the method above, using a single key instead of a range. I wouldn't fire you for testing that, though.
On my desktop, I can insert 100k rows in 1.455 seconds, and update 100k rows with a simple calculation in 420 ms. If you're running on a phone, that's probably not relevant.
Comment for this answer: I do not have an integer key. I tried using rowid instead. I had to make select max(rowid), then make a loop from 0 to this value with some step, like 10000.
On a freshly generated test table this gives pretty good results. max(rowid) is equal to the number of records and the number of required update queries is equal to number of records / 10000.
On 1 mln records the entire loop is just 50% slower than a single query. But after numerous deletes/inserts max(rowid) will be much larger than number of records and number of queries will be much larger and performance will be much worse, I guess.
Comment for this answer: When I wrote "But I cannot start a new transaction for each row, can I?" I did not mean making a new query for each row, but somehow make UPDATE query automatically start an implicit transaction for each row. But this is impossible, I guess.
Comment for this answer: My keys are file paths. Hard to make predictable ranges with them.
Comment for this answer: SQL is a set-oriented language. In a single statement, there's no such thing as "each row". There's only "this set of rows". You don't have to use an integer key; use whatever key you have for the range.

Here is another answer: I also posted this question at
http://thread.gmane.org/gmane.comp.db.sqlite.general/81946
and got several interesting answers, such as:
divide range of rowid into slices and update one slice at a time
use AUTOINCREMENT feature to start new update at the place where the previous update ended (by LIMIT 10000)
create a trigger that calls select raise(fail, ...) to abort update without rollback

Here is another answer: You mentioned poor performance with LIMIT. Do you have a lastupdated column with an index on it? At the top of your procedure you would get the COMMENCED_DATETIME and use it for every batch in the run:
update foo
set myvalue = 'x', lastupdated = UPDATE_COMMENCED
where id in
select id from foo where lastupdated < UPDATE_COMMENCED
limit SOME_REASONABLE_NUMBER
P.S. With respect to slowness:
I also tried UPDATE with LIMIT and also WHERE custom_condition(C1). These approaches do allow me to terminate update earlier, but they are significantly slower than regular update...
If you're willing to give other processes access to stale data, and your update is designed so as not to hog system resources, why is there a need to have the update complete within a certain amount of time? There seems to be no need to worry about perfomance in absolute terms. The concern should be relative to other processes -- make sure they're not blocked.
Comment for this answer: So, you want me to:
1. Add column "lastupdated INTEGER default 0" to table T
2. Add index CREATE INDEX Idx_lastupdated ON T(lastupdated)
3. Increment variable UPDATE_COMMENCED before running update loop
and add nested select ... where lastupdated < UPDATE_COMMENCED
It works, but:
- original insertion is 33% slower due to the extra index
- update is 3 times slower than rowid between x and y suggested by Mike Sherrill
and 4 times slower than a version without any limits
Comment for this answer: Also it appears that this solution is unnecessarily complicated.
lastupdated  column is not really needed.
select rowid from T where lastupdated < UPDATE_COMMENCED
can be replaced by
select rowid from T where C1 is NULL.
The timing is identical to the lastupdated case.
Comment for this answer: The index causes original table creation to slow down by 33%,
which is unacceptable.
The index also causes update of 100,000 record to slow down by 50%
But index causes update of 1 mln records to speed up by 25%.
Most importantly, without index the time of each query varies
between 1 and 4.5 seconds (which comes close to 5 second timeout),
but with index the time stays constant at 2 seconds.
So, without index I may get timeouts, but with index
I get a huge performance hit.
Comment for this answer: You wrote: "If you're willing to give other processes access to stale data, ..., why is there a need to have the update complete within a certain amount of time?"
Well, user understands that it takes time to prepare the data, but if data readiness will be delayed 3 or 4 times, it will be very noticeable.
Comment for this answer: "You did not say that you needed to create a table" - obviously, this 1 mln records have to be first inserted into the table. And speed of this initial insertion is more important for me than update speed, so index is unacceptable.
Comment for this answer: `The index causes original table creation to slow down by 33%`. You did not say that you needed to create a table. `index causes update of 1 mln records to speed up by 25%`. Exactly.
Comment for this answer: `with index the time stays constant at 2 seconds`. With index you get predictability. Pick a batch size accordingly.
Comment for this answer: You can either maximize performance during the update, by blocking other processes, or have multiple processes share the burden. You cannot have your cake and eat it too.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:06/14/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sqlite>', 'Title': 'Sqlite3: How to interrupt a long running update without roll back?', 'ViewCount': 966, 'id': 17099491, 'user_AboutMe': '', 'user_AccountId': 480849.0, 'user_DisplayName': 'jhnlmn', 'user_Reputation': 381.0, 'user_Views': 68.0, 'user_id': 894324.0}}