Coroutine cancellation in room database operations

I have a screen with a corresponding ViewModel. This screen is a form to be filled and then recorded in a Room database.

After each insertion, some other tables have to be updated accordingly as well.

I have this function in my repository:

 suspend fun writeTransactionToDatabase(transaction: Transaction) {
        db.withTransaction { // <-- "magic" solution
            transactionDao.insert(transaction)
            updateCategoryAndValue(transaction)
            updateBalance(transaction)
        }
    }

where updateCategoryAndValue and updateBalance are both suspend functions. I call this function from my viewmodel and immediately navigate away. It is my understanding that this means the viewmodel is destroyed. And since I called this method like so:

 viewModelScope.launch {
     repository.writeTransactionToDatabase(transaction)
 }

I am assuming this means that function is canelled when the viewmodel is destroyed. However by adding withTransaction somehow the bug appears to be fixed.

I want to know how does this cancellation work in this case, does room continue the current transaction if it has started and withTransaction makes it atomic so somehow it now finishes?

I have ran this in a debugger, and without it, the function appears to be cancelled halfway through the body of the third called function, namely updateBalance(). And when I added the withTransaction, the UI is not shown and the function seems to continue until termination.

Can someone explain what is going on here ? Thanks in advance.

Adding to the answer by @MikeT, I think the root cause of this behavior is located in the RoomDatabase.startTransactionCoroutine():

suspendCancellableCoroutine { continuation ->
    try {
        transactionExecutor.execute {
            try {
                ...
            } catch (ex: Throwable) {
                // If anything goes wrong, propagate exception to the calling coroutine.
                continuation.cancel(ex)
            }
        }
    } catch (ex: RejectedExecutionException) {
        // Couldn't acquire a thread, cancel coroutine.
        continuation.cancel(
            IllegalStateException(
                "Unable to acquire a thread to perform the database transaction.", ex
            )
        )
    }
}

This code fragment looks strange to me. They schedule the execution on top of their own executor. They properly pass failures from the executor to coroutine, but they don't do the other way around - they ignore coroutine cancellations.

During regular execution, it properly waits for the operation to finish and only then return from the suspend function. But in the case of a cancellation, function returns immediately, while the operation is still running in the background. So cancellation doesn't cancel the operation, it only detaches waiting for it.

I don't know if this is intentional or some kind of a bug, but at least for me this behavior is very confusing. First, cancellation doesn't cancel. Second, behavior is inconsistent: sometimes it waits for the operation to finish and sometimes it runs it in the background.