Databases
StackOverflow2013
Postsdbo
POSqlite3: How to interrupt a long running update without roll back?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSqlite3: How to interrupt a long running update without roll back?
    primarykey
    data
    text
    <p>I have a long running multirow update such as:</p> <pre><code> UPDATE T set C1 = calculation(C2) where C1 is NULL </code></pre> <p>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).</p> <p>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).</p> <p>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?</p> <p>If interrupt and progress_handler cannot help me, what else I can do?</p> <p>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).</p> <p>Any other ideas? This multirow update is such a common operation that, I hope, other people have a good solution for it.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USjhnlmn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSqlite3: How to interrupt a long running update without roll back?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSqlite3: How to interrupt a long running update without roll back?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COMulti-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.
      singulars
      1. POSqlite3: How to interrupt a long running update without roll back?
      1. USTim
    2. COYes, 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.
      singulars
      1. POSqlite3: How to interrupt a long running update without roll back?
      1. USjhnlmn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload