Windows/SQLite: Can supervised quiescence prove safe resource release when native failures are hidden?

Hi @Carlos Garcia ,

Supervised quiescence can be defended if the claim is narrowed to three things: every authorized owner was started inside the containment boundary and has exited, no known owner can reopen the files, and the storage postconditions you defined passed afterwards. It cannot stand in for proof that each individual native release succeeded.

To see how the mechanisms actually behave, I ran a small repro on Windows 11 Pro x64 build 26300 with CPython 3.13.12 and SQLite 3.50.4. The databases were disposable, in WAL and DELETE mode with synchronous=FULL. Each worker was created suspended, assigned to a job that only had JOB_OBJECT_LIMIT_KILL_ON_JOB_CLOSE set, and then resumed. The test job was nested under an existing job. Since this is not your build, your Python version, or your application, the results describe mechanism behavior rather than a root cause in your platform.

The TotalProcesses=2 and ActiveProcesses=1 pattern reproduced right after the worker exited. The remaining member was conhost.exe, which appeared because the console worker was launched with CREATE_NO_WINDOW. It did not appear with DETACHED_PROCESS. A venv worker added one more member, because .venv\Scripts\python.exe started the base python.exe as its child. On this build, the supervisor keeping the worker's process handle open did not hold ActiveProcesses above 0.

CREATE_BREAKAWAY_FROM_JOB failed with ERROR_ACCESS_DENIED (5). A child created through WMI was outside the job and survived when the job was killed, and a process that had opened the database before the job existed kept it open as well. After a file handle was duplicated into a process outside the job, ActiveProcesses reached 0 while the database remained open, and RmGetList reported that outside process.

While an owner had the files open, an exclusive open (CreateFileW with dwShareMode set to 0) on the database, -wal, -shm, and -journal failed with ERROR_SHARING_VIOLATION (32). After close or kill, it succeeded. A non-blocking LockFileEx on SQLite's lock bytes failed with ERROR_LOCK_VIOLATION (33) during an open transaction. The first successful probe came about 3 ms after the terminate call, a figure that includes the wait and the probe itself, so it is not the exact release time. With mmap_size enabled, SetEndOfFile failed with ERROR_USER_MAPPED_FILE (1224) on the database and on -shm while a view existed, and it succeeded once the worker exited. In a case where only the view was left, after its file and mapping handles had been closed, the exclusive open succeeded even though truncation still failed.

The SQLite file handles in the worker were synchronous (FILE_SYNCHRONOUS_IO_NONALERT). During a write loop, the process handle became signaled about 77 ms after TerminateProcess was called. I did not capture a trace showing a request pending at that moment, so that part is still unverified. In both WAL and DELETE mode, clean close and forced kill both reopened with integrity_check returning ok. Committed rows were kept, and uncommitted changes were rolled back. The kill path left -wal and -shm, or -journal, on disk.

So is end-state quiescence defensible? Yes, as long as the claim is about contained owners and verified postconditions, not about how each release ended. The Remarks section of TerminateProcess is the documented basis. Termination is asynchronous, a terminated process cannot exit until all pending I/O has been completed or canceled, and you confirm termination by waiting on the process handle. Once that handle is signaled, no pending I/O from the process remains, but the handle does not tell you how each request ended.

No single supported mechanism verifies every resource type. An exclusive open catches open handles, but not a view that outlived its handles. SetEndOfFile catches a remaining view, and the Remarks section of SetEndOfFile explains why: all views must be unmapped and the mapping object closed first. Because it truncates the file, this probe is only suitable for disposable data. A non-blocking lock probe catches held byte ranges. The Remarks section of LockFile states two relevant points. First, the time the system takes to release locks after termination depends on available resources. Second, a byte-range lock does not prevent reads or writes through a mapped view. The lock probe therefore needs a deadline and cannot replace a mapping check. RmGetList only reports processes using resources registered with its session. For pending I/O, waiting on the process handle is the supported boundary. CancelIo does nothing on a handle opened without FILE_FLAG_OVERLAPPED.

An access fence across every SQLite owner has to exist from launch time. A job covers only processes that are assigned to it or created by its members through CreateProcess. The Managing Processes in Jobs section of Job Objects also notes that children created through Win32_Process.Create are not associated with the job, which matches what I saw. In practice, the supervisor starts every opener inside the job, including openers that fail initialization. Database handles are created non-inheritable and are never passed with DuplicateHandle. Nothing starts an opener through WMI, a service, or a scheduler. When several owners open the same database, the fence only holds if every one of them is launched inside the same job, so the claim for each database has to cover all of its openers rather than one owner at a time. Any process that existed before the fence needs positive evidence that it does not hold the files. To pin down the termination domain before enabling kill on close, enumerate members with JobObjectBasicProcessIdList (JOBOBJECT_BASIC_PROCESS_ID_LIST), which for a nested job also includes processes in its child jobs. Then attribute each image and parent PID. The counters are not enough on their own, since TotalProcesses in JOBOBJECT_BASIC_ACCOUNTING_INFORMATION also counts processes that have already terminated.

The claim should explicitly exclude whatever keeps quiescence from being equivalent. That covers processes outside the job (pre-existing, created through WMI, or holding a duplicated handle), views an exclusive open cannot see, the outcome of each I/O request at kill time, and lock release timing. It also covers durability beyond what SQLite and the flush calls guarantee, and any open after the final probe, since each probe only reflects one point in time. Sidecar files left after a kill do not by themselves mean an owner is still alive. A clean shutdown can only be told apart from a forced one by an acknowledgement the worker sends after closing the connection.

Everything above is what I could observe and verify within the scope of this Microsoft Q&A forum, on the environment described and with documented APIs. I hope these results give you a firmer basis for deciding on the acceptance requirement change, with a clearer line between what the repro confirmed on this build, what rests on the documentation alone, and what is still unverified, such as the outcome of each I/O request at kill time.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guidance so that others in the community facing a similar issue can find it more easily and help each other.

Thank you.