SQLite Forum

Possible freeze in the progress loop

Possible freeze in the progress loop

(1) By Max (Maxulite) on 2020-07-06 10:22:40 [source]


When I insert a comparatively large dataset into a table (about 600 millions records) I encounter a "freeze" at some time during the insert. Since I have a progress callback, the "freeze" does not make the program unresponsive, only nothing happens with the VFS traffic (I have a vfs read/write numbers constantly showing the data) and with memory allocations. Windows process info also shows "freezing" for different I/O numbers.

When I made some debugging, the code that waits "forever" is probably this ( from SQLITE_PRIVATE int sqlite3VdbeExec )

while( nVmStep>=nProgressLimit && db->xProgress!=0 ){ assert( db->nProgressOps!=0 ); nProgressLimit += db->nProgressOps; if( db->xProgress(db->pProgressArg) ){ nProgressLimit = 0xffffffff; rc = SQLITE_INTERRUPT; goto abort_due_to_error; } }

In my case I see that

  • nVmStep = 0xFFFFFFFE
  • nProgressLimit is increasing by db->nProgressOps = 0xA.

Looking at the condition and taking the 32-bit wrapping logic into account, nProgressLimit will probably never be greater than the current nVmStep value (the only greater value is the odd 0xFFFFFFFF and no even to even wrapping (+0xA) will lead to it)


(2) By Richard Hipp (drh) on 2020-07-06 12:15:29 in reply to 1 [link] [source]

Please try again using the latest prerelease snapshot and let us know if you continue to have problems.

(3) By Max (Maxulite) on 2020-07-06 14:42:43 in reply to 2 [link] [source]


everything is ok, the insert is completed without freezing.

I wonder whether the counter resolution increase to 64 bit can have performance penalty on some 32-bit compilers/architectures. I hope not given that a single vdbe instruction probably translates into multiply assembler instructions

(4) By Max (Maxulite) on 2020-07-07 10:49:39 in reply to 2 [link] [source]

I also tried to make some simple test with a small memory/time footprint using CTE and noticed that due to the logic behind the progress callback, the unexpected outcome could also be a partial freeze of the progress callback while Sqlite still continues the execution and finishes it.

This is probably because not every nVmStep increase goes to progress handler, but probably only "Goto" vdbe instruction. So nProgressLimit may have some almost fully saturated value (0xFFFFFFFA) and nVmStep leaves the progress loop (for example with 0xFFFFFFF9 value) and wraps after that to zero (while not visiting the progress loop), so after that this progress loop is ignored until nVmStep reaches again an big value.

So some other users previously could have not a responsive-freeze symptom like mine, but one when sqlite becomes fully unresponsive starting some point during the execution, but finally finishing without an issue.