App crashes when canceling a task that contains an asynchronous GRDB write operation

[sepw]

I start a task that writes to a database queue asynchronously using:

let task = Task {
  try await queue.write {
    ...
    try Task.checkCancellation()
    ...
  }
}

I'm attempting to cancel the task using

task.cancel()

and the app crashes with the following error:

GRDB/SerializedDatabase.swift:261: Fatal error: A transaction has been left opened at the end of a database access

I only get this fatal error from GRDB when the task is cancelled. The write transaction is rolled back correctly when I throw an application-defined error from the closure passed to the database writer's write(_:) function:

let task = Task {
  try await queue.write {
    ...
    throw AppError.oops
    ...
  }
}

According to the GRDB documentation for DatabaseWriter's func write(_:) async throws, cancellation of a task performing a write is legal and supported:

extension DatabaseWriter {

    /// ...
    /// - throws: Any ``DatabaseError`` that happens while establishing the
    ///   database access, or the error thrown by `updates`, or
    ///   `CancellationError` if the task is cancelled.
    public func write<T: Sendable>(
        _ updates: @Sendable (Database) throws -> T
    ) async throws -> T
}

Is there something I should be doing to handle cancellation errors in asynchronous database read or write closures?

GRDB version: 7.8.0.

[groue]

Hello @sepw,

Thanks for the report, it definitely looks like a bug. Do you happen to have a reproducing scenario? This would definitely help.

[sepw]

Hi @groue

Thanks for the quick response.

Thanks for the report, it definitely looks like a bug. Do you happen to have a reproducing scenario? This would definitely help.

I have a fair amount of infrastructure built on top of GRDB, so I can't easily provide a simple reproducing scenario. I will try and strip my code back to produce a simpler example that triggers the fatal error. In the meantime:

I am experiencing the fatal error in a unit test that

1. creates an in-memory database queue;
2. creates the database schema;
3. starts a task that reads and writes to/from the DB;
4. cancels the task after a second and waits for the canceled task to complete; and
5. tears down in-memory database queue.

The call stack of the fatal error is:

#4	GRDBPrecondition(_:_:file:line:) at GRDB.swift/GRDB/Utils/Utils.swift:51
#5	SerializedDatabase.preconditionNoUnsafeTransactionLeft(_:_:file:line:) at GRDB.swift/GRDB/Core/SerializedDatabase.swift:303
#6	SerializedDatabase.execute<A>(_:) at GRDB.swift/GRDB/Core/SerializedDatabase.swift:261
#7	closure #1 in SerializedDatabase.execute<(returnValue: (), notification: Store.Notification)>(_:) ()
#8	closure #1 in SerializedDatabase.execute<A>(_:) ()
#9	DispatchQueueActor.execute<(returnValue: (), notification: Store.Notification)>(_:) at GRDB.swift/GRDB/Core/DispatchQueueActor.swift:19
#10	closure #1 in SerializedDatabase.execute<(returnValue: (), notification: Store.Notification)>(_:) at GRDB.swift/GRDB/Core/SerializedDatabase.swift:257
#11	closure #1 in SerializedDatabase.execute<A>(_:) ()
#12	await resume partial function for Swift.withTaskCancellationHandler<τ_0_0>(operation: () async throws -> τ_0_0, onCancel: @Sendable () -> (), isolation: isolated Swift.Optional<Swift.Actor>) async throws -> τ_0_0 ()
#13	withTaskCancellationHandler<τ_0_0>(operation:onCancel:isolation:) ()
#14	SerializedDatabase.execute<(returnValue: (), notification: Store.Notification)>(_:) at GRDB.swift/GRDB/Core/SerializedDatabase.swift:256
#15	DatabaseQueue.writeWithoutTransaction<(returnValue: (), notification: Store.Notification)>(_:) at GRDB.swift/GRDB/Core/DatabaseQueue.swift:402
#16	protocol witness for DatabaseWriter.writeWithoutTransaction<A>(_:) in conformance DatabaseQueue ()
#17	DatabaseWriter.write<GRDB.DatabaseQueue>(_:) at GRDB.swift/GRDB/Core/DatabaseWriter.swift:645
#18	Store.write<()>(_:) at Store+Transactions.swift:71
#19	closure #1 in DemoProvider.start() at DemoProvider.swift:79
#20	partial apply for closure #3 in closure #1 in DemoProvider.start()

[sepw]

One other thing:

The fatal error still occurs if the test case waits for a number of seconds after the canceled task completes before tearing down the database queue, so I suggest a race condition in database closure can probably be excluded.

[groue]

Thanks for the feedback. It's extremely precious that you can reproduce in a unit test 👍 I understand that it's not always easy to reduce it in a way that can be shared. But we have something already.

Before your reply, your mention of error in the original message made me think of a case that is not properly handled today. Database cancellation interrupts SQLite operations, and if it is a rollback operation that is interrupted, I'm afraid that GRDB currently does not handle this properly. The database access ends with a transaction that is still open, and the precondition kicks in. Do you think this could be a scenario triggered by your test case? Is there any explicit or implicit rollback there?

[sepw]

Before your reply, your mention of error in the original message made me think of a case that is not properly handled today. Database cancellation interrupts SQLite operations, and if it is a rollback operation that is interrupted, I'm afraid that GRDB currently does not handle this properly. The database access ends with a transaction that is still open, and the precondition kicks in. Do you think this could be a scenario triggered by your test case? Is there any explicit or implicit rollback there?

The cancellation is interrupting a transaction that is composed of reads and writes. The only implicit rollback is the one triggered by the cancellation. There is no explicit or implicit rollback triggered by application code.

[groue]

There is no explicit or implicit rollback triggered by application code.

All right. I'll address that cancelled rollback scenario anyway, since I'm now aware of a potential issue here.

But we did not get closer to the root cause of your failing test. Even if you can't share code, maybe you can share something? A pattern of code? Something that reproduces the issue and that I could chew on, you see?

[sepw]

I am struggling to understand the following from SerializedDatabase:

    func execute<T: Sendable>(
        _ block: @Sendable (Database) throws -> T
    ) async throws -> T {
        let cancelMutex = Mutex<(@Sendable () -> Void)?>(nil)
        return try await withTaskCancellationHandler {
            try await actor.execute {
                defer {
                    cancelMutex.store(nil)
                    db.uncancel()
                    preconditionNoUnsafeTransactionLeft(db)
                }
                cancelMutex.store(db.cancel)
                try Task.checkCancellation()
                return try block(db)
            }
        } onCancel: {
            cancelMutex.withLock { $0?() }
        }
    }

What I do understand:

• SerializedDatabase is responsible for executing database access closures in a serial manner
• It does this using DispatchQueueActor, which dispatches work on a LibDispatch queue that I presume is serial (it's in the name!), although I couldn't verify with on a cursory glance at the code as the configuration of the queue is fairly involved

So, Swift's Structured Concurrency (i.e. TaskGroup) is not involved in the execution of database work. An unstructured Task also isn't involved, and neither is a detached Task. Instead, Swift code is routed through non-Swift code and dispatched by LibDispatch.

It's my understanding (and please correct me if I'm wrong) that task cancellation is only guaranteed to be propagated correctly across task boundaries when Swift's Structured Concurrency is used (i.e. TaskGroup). My experience corroborates this, with manual cancel propagation required to cancel unstructured tasks via withTaskCancellationHandler(_:onCancel:). But there is no unstructured or detached Task in play here, only LibDispatch, so this is neither structured nor unstructured concurrency, i.e. database access is executed outside the purview of Swift Concurrency altogether.

So my question is this:

While cancellation of the calling task is handled by SerializedDatabase.execute(_:), with GRDB attempting to cancel the current database access via SQLite, is this cancellation actually detectable by the specified work block that is executed by LibDispatch? i.e. Does a call to Task.checkCancellation() in the database access block passed to DatabaseWriter.write(_:) actually throw?

I investigated this with the following test case

@Test func cancelTransaction() async throws {
  let queue = try DatabaseQueue()
  
  let task = Task {
    try await queue.write {
      do {
        try $0.execute(literal: "CREATE TABLE person (id INT)")
        
        for i in 1...100 {
          print("inserting \(i)...")
          try $0.execute(literal: "INSERT INTO person (id) VALUES (\(i))")
          print("inserted.")
          print("sleeping...")
          Thread.sleep(forTimeInterval: 0.1)
          print("slept.")
          print("checking cancellation...")
          try Task.checkCancellation()
          print("continuing...")
        }
      } catch {
        print("error caught: \(error)")
        throw error
      }
    }
  }
  try await Task.sleep(for: .seconds(0.5))
  
  print("cancelling...")
  task.cancel()
  print("canceled.")
  
  let _ = await task.result
  
  print("completed.")
}

which produces the following output

inserting 1...
inserted.
sleeping...
slept.
checking cancellation...
continuing...
inserting 2...
inserted.
sleeping...
slept.
checking cancellation...
continuing...
inserting 3...
inserted.
sleeping...
slept.
checking cancellation...
continuing...
inserting 4...
inserted.
sleeping...
slept.
checking cancellation...
continuing...
inserting 5...
inserted.
sleeping...
cancelling...
canceled.
slept.
checking cancellation...
error caught: CancellationError()
completed.

i.e. the task that performs queue.write(_:) does respond to cancellation by throwing CancellationError ✅, and it does so likely because there is no Swift Concurrency task boundary between the task calling SerializedDatabase.execute(_:) and the LibDispatch queue that actually runs the access closure, despite a thread boundary possibly being present.

This seems more accidental than by design, and I'm concerned that this works when LibDispatch executes the closure on the calling thread (LibDispatch can execute on any thread, the only requirement being for a serial queue that work items are executed in sequence and do not overlap), but is this guaranteed to work for items executed asynchronously on a separate thread? I don't know: my understanding of the interaction of Swift Tasks and LibDispatch fails me here.

What I don't entirely understand here is why GRDB contains special-case handling for cancellation in SerializedDatabase.execute(_:): If cancellation is co-operative, as prescribed by Swift Concurrency, then a task (and I use the term loosely here to include a closure executed by SerialDatabase) should have to check for cancellation by calling Task.checkCancellation(), which would throw a cancellation error that would, in turn, trigger rollback in the same manner as the detection of any other database- or application-defined error thrown by the closure.

But then I noticed that my test case still cancels if I remove the call to Task.checkCancellation(), throwing a CancellationError from the call to $0.execute(literal: "..."), i.e.

checking cancellation...
continuing...
inserting 6...
error caught: CancellationError()

My interpretation is that GRDB's special-case cancellation handling is cancelling the underlying SQLite database access in order to pro-actively respond to cancellation on the application's behalf.

Is this correct? If so, then I'm not entirely sure this is the correct strategy. I would venture that it runs counter to the cooperative cancellation model--at least from the perspective of the application--but that is probably another discussion altogether and likely unrelated to the crash I've observed. I investigated this behavior and ask purely to further my understanding of how cancellation is implemented by GRDB...

[sepw]

But we did not get closer to the root cause of your failing test. Even if you can't share code, maybe you can share something? A pattern of code? Something that reproduces the issue and that I could chew on, you see?

I have spent the last couple of hours investigating GRDB's cancellation model (see the above). I will now turn my attention to attempting to reproduce the fatal error I'm observing...

[sepw]

The issue seems to be related to the use of a virtual FTS5 table.

Here is a test case that reliably produces the fatal error. Note that the fatal error does not occur if the FTS5 table is not inserted by removing lines 87-88:

try PersonText(id: person.id, content: person.name)
  .insert(database)

Here is the test code in its entirety.

import Foundation
import GRDB
import os
import Testing

struct Person: Codable, TableRecord, FetchableRecord, PersistableRecord {
  static let databaseTableName = "person"
  
  var id: Int
  var name: String
  
  enum Column: String, CodingKey, ColumnExpression {
    case id
    case name
  }
  typealias CodingKeys = Column
}

// MARK: -

struct PersonText: Codable, TableRecord, FetchableRecord, PersistableRecord {
  static let databaseTableName = "person_text"
  
  let id: Int
  let content: String
  
  enum Column: String, CodingKey, ColumnExpression {
    case id
    case content
  }
  typealias CodingKeys = Column
}

// MARK: -

final class SQLiteStore: Sendable {
  private let queue: DatabaseQueue
  
  init(queue: DatabaseQueue) {
    self.queue = queue
  }
  
  convenience init() throws {
    let queue = try DatabaseQueue()
    self.init(queue: queue)
    
    var migrator = DatabaseMigrator()
    migrator.registerMigration("Person/v1") { db in
      try db.create(table: Person.databaseTableName) { t in
        t.column(Person.Column.id.name, .integer).notNull().primaryKey()
        t.column(Person.Column.name.name, .text).notNull()
      }
      try db.create(virtualTable: PersonText.databaseTableName, using: FTS5()) { t in
        t.tokenizer = .porter(wrapping: .unicode61())
        t.column(PersonText.Column.id.name).notIndexed()
        t.column(PersonText.Column.content.name)
      }
    }
    try migrator.migrate(queue)
  }
  
  // MARK: -
  
  class Reader {
    let database: Database
    
    init(database: Database) {
      self.database = database
    }
    func fetchPersons() throws -> [Person] {
      return try Person
        .fetchAll(database)
    }
    func personExists(id: Int) throws -> Bool {
      return try Person
        .filter(Person.Column.id == id)
        .fetchCount(database) > 0
    }
  }
  
  // MARK: -
  
  final class Writer: Reader {
    func insert(_ person: Person) throws {
      try person
        .insert(database)
      try PersonText(id: person.id, content: person.name)
        .insert(database)
    }
    func ensure(_ id: Int, inserting person: @autoclosure () -> Person) throws {
      if try !personExists(id: id) {
        try insert(person())
      }
    }
  }
  
  // MARK: -
  
  func read<T>(_ body: @Sendable @escaping (Reader) throws -> T) async throws -> T where T: Sendable {
    return try await queue.read {
      try body(Reader(database: $0))
    }
  }

  func write<T>(_ body: @Sendable @escaping (Writer) throws -> T) async throws -> T where T: Sendable {
    return try await queue.write {
      try body(Writer(database: $0))
    }
  }
}

// MARK: -

struct GRDBTests {
  @Test func cancelWrite() async throws {
    let store = try SQLiteStore()
    
    let task = Task {
      do {
        try await store.write {
          for i in 1... {
            try $0.ensure(i, inserting: Person(id: i, name: "person \(i)"))
            Thread.sleep(forTimeInterval: 0.1)
            try Task.checkCancellation()
          }
        }
      } catch {
        print("caught \(error)")
        throw error
      }
    }
    
    try await Task.sleep(for: .seconds(1))
    
    task.cancel()
    let _ = await task.result
  }
}

The test output is:

Test run started.
Testing Library Version: 124.4
Target Platform: arm64e-apple-macos14.0
Suite GRDBTests started.
Test cancelWrite() started.
GRDB/SerializedDatabase.swift:261: Fatal error: A transaction has been left opened at the end of a database access

[groue]

Here is the test code in its entirety.

🤩 Thank you so much!! I can reproduce, so a fix will come rather quickly.

I am struggling to understand the following from SerializedDatabase:

Thanks also for looking at the code - multiple eyes can only help 👍

What I do understand:

• SerializedDatabase is responsible for executing database access closures in a serial manner

Yes, exactly. GRDB opens SQLite connections in the "Multi-thread" threading mode which supports using the same connection from multiple threads, provided that a single thread uses it at any given time. SerializedDatabase wraps all uses of an SQLite connection in a serial DispatchQueue.

The advantage of a DispatchQueue over Swift concurrency constructs is that it supports synchronous database accesses.

• It does this using DispatchQueueActor, which dispatches work on a LibDispatch queue that I presume is serial (it's in the name!), although I couldn't verify with on a cursory glance at the code as the configuration of the queue is fairly involved

DispatchQueueActor is a Swift actor whose executor is the serial DispatchQueue mentioned above. Executors are a Swift concurrency feature.

The goal here is to guarantee serialization with the DispatchQueue, and at the same time support Swift concurrency via its actor+executor system.

So, Swift's Structured Concurrency (i.e. TaskGroup) is not involved in the execution of database work. An unstructured Task also isn't involved, and neither is a detached Task. Instead, Swift code is routed through non-Swift code and dispatched by LibDispatch.

Yes, exactly. We stay in the current task, with the purpose of inheriting cancellation as we'll see below.

It's my understanding (and please correct me if I'm wrong) that task cancellation is only guaranteed to be propagated correctly across task boundaries when Swift's Structured Concurrency is used (i.e. TaskGroup). My experience corroborates this, with manual cancel propagation required to cancel unstructured tasks via withTaskCancellationHandler(_:onCancel:). But there is no unstructured or detached Task in play here, only LibDispatch, so this is neither structured nor unstructured concurrency, i.e. database access is executed outside the purview of Swift Concurrency altogether.

You get it right, except for one point. Even if the database access is indeed executed in a DispatchQueue, it remains under the Swift concurrency realm, through the custom executor. I may sound assertive here, but please be assured I felt the same thrill of doubt as you do.

So my question is this:

While cancellation of the calling task is handled by SerializedDatabase.execute(_:), with GRDB attempting to cancel the current database access via SQLite, is this cancellation actually detectable by the specified work block that is executed by LibDispatch? i.e. Does a call to Task.checkCancellation() in the database access block passed to DatabaseWriter.write(_:) actually throw?

I investigated this with the following test case [...] the task that performs queue.write(_:) does respond to cancellation by throwing CancellationError ✅, and it does so likely because there is no Swift Concurrency task boundary between the task calling SerializedDatabase.execute(_:) and the LibDispatch queue that actually runs the access closure, despite a thread boundary possibly being present.

That's the kind of tests I ran too.

This seems more accidental than by design, and I'm concerned that this works when LibDispatch executes the closure on the calling thread (LibDispatch can execute on any thread, the only requirement being for a serial queue that work items are executed in sequence and do not overlap), but is this guaranteed to work for items executed asynchronously on a separate thread? I don't know: my understanding of the interaction of Swift Tasks and LibDispatch fails me here.

Not in my interpretation. But I do totally understand the question. And maybe I will have to update my understanding after I could understand the failing test case you gently provided.

What I don't entirely understand here is why GRDB contains special-case handling for cancellation in SerializedDatabase.execute(_:): If cancellation is co-operative, as prescribed by Swift Concurrency, then a task (and I use the term loosely here to include a closure executed by SerialDatabase) should have to check for cancellation by calling Task.checkCancellation(), which would throw a cancellation error that would, in turn, trigger rollback in the same manner as the detection of any other database- or application-defined error thrown by the closure.

My interpretation is that GRDB's special-case cancellation handling is cancelling the underlying SQLite database access in order to pro-actively respond to cancellation on the application's behalf.

Is this correct? If so, then I'm not entirely sure this is the correct strategy. I would venture that it runs counter to the cooperative cancellation model--at least from the perspective of the application--but that is probably another discussion altogether and likely unrelated to the crash I've observed. I investigated this behavior and ask purely to further my understanding of how cancellation is implemented by GRDB...

I understand and pounder that "cancellation is co-operative". But this is no reason for not helping developers. And there are multiple async apis in the stdlib that handle cancellation "on the application's behalf". For example, no one has to perform explicit cancellation checks when one calls Task.sleep.

SE-03024 says:

[...] The general expectation is that asynchronous functions should attempt to respond to cancellation by promptly throwing or returning.

I thought GRDB could help here, by following the "general expectation". So I made try await read/write { ... } throw when the task is cancelled.

The "promptly" word had me go farther: task cancellation sends an SQLite interrupt that makes the current SQLite execution fail, if any. This has SQLite abort immediately, even when it is in the middle of a slow database query. I thought it was a nice addition. Something that would be difficult to do in a GRDB-powered app, just as it would be difficult to do it with Task.sleep if it weren't baked in.

Eventually cancellation handling sounds like a welcome feature to me.

Now... Supporting cancellation correctly has not been an easy task, and I'm sorry we're having this conversation today. I mean, I enjoy it, but I regret you had to meet an issue in the first place 😅.

[sepw]

The "promptly" word had me go farther: task cancellation sends an SQLite interrupt that makes the current SQLite execution fail, if any. This has SQLite abort immediately, even when it is in the middle of a slow database query. I thought it was a nice addition. Something that would be difficult to do in a GRDB-powered app, just as it would be difficult to do it with Task.sleep if it weren't baked in.

Eventually cancellation handling sounds like a welcome feature to me.

That was my assumption based on my analysis of the code. Thanks for spelling it out.

The strategy currently implemented (i.e. GRDB basically taking on responsibility for cancellation through SQLite) obviously has some distinct advantages:

• It's baked in and will therefore work for all clients, not just those that implement their access closures to be cancellation-aware
• Cancellation through SQLite can, as you stated, interrupt lengthy query execution and respond potentially faster than the calling application code. This is a definite win.

I'm not sure I entirely agree that this is the correct model for an application. For example, you make the comparison to Task.sleep(for:):

For example, no one has to perform explicit cancellation checks when one calls Task.sleep.

I don't think this comparison holds as Task.sleep(for:) is

1. an integral part of Swift's task execution framework; and
2. atomic.

DatabaseWriter.write(_:) is not atomic. Conceptually it's composed of a sequence of application-defined statements that provide the application the opportunity to respond to cancellation. This is very different.

But, I appreciate that a framework such as GRDB is caught between the devil and deep blue sea here: some developers will feel relieved that cancellation is not something they have to worry about, while others will feel that cancellation should be under the complete control of the application. I happen to be in the latter camp, but that doesn't mean that I'm right!

I'm not saying that GRDB shouldn't have implemented the strategy that it did. GRDB is a very well developed framework, with ample evidence that a great deal of thought was invested into all aspects of the code base, and I don't doubt that this applies to cancellation too. I may not have made the same decision, but I'm glad you made a considered and opinionated decision. I'm not suggesting that GRDB's cancellation behavior is incorrect (it isn't: it's perfectly valid), and certainly not that it be changed.

[groue]

Thank you for your nice words :-)

Conceptually it's composed of a sequence of application-defined statements that provide the application the opportunity to respond to cancellation. This is very different.

I really agree, and you are pointing at doubts I do share with you... I've played a dangerous game with POLA here.

Let's imagine that you did not meet the bug in the first place. Cancellation would have been handled — properly. The task is cancelled, the write fails, and CancellationError is thrown. Do you still think you would have been surprised, considering you would have less information because you would not have studied the internals of the library?

My bet was that more people will not spend more than two seconds thinking about it:

• "Oh, CancellationError, sure, the task was cancelled".
• "Was it a cancelled URLSession request? Oh no, it was the subsequent database write. Nice."

As for the Task.sleep comparison, maybe it was a little far-stretched, but it still stands somehow, because manually cancelling slow database jobs is really not easy. SQLite interrupt is a signal ¹, not a state. It has to be spawned during a database job in order to have any effect:

try await dbQueue.write { db in
  try slowWrite(db, ...) // <- SQLite interrupt would stop this
  try doSomethingElse()  // <- SQLite interrupt would NOT stop this
  try slowRead(db, ...)  // <- SQLite interrupt would stop this
}

Poor users, how are they supposed to reliably interrupt such a database access? I think they'd have to setup a cancellation handler that calls the GRDB interrupt api, and add cancellation checks in their code:

try await withTaskCancellationHandler {
  try await dbQueue.write { db in
    try Task.checkCancellation()
    try slowWrite(db, ...)
    try Task.checkCancellation()
    try doSomethingElse()
    try Task.checkCancellation()
    try slowRead(db, ...)
  }
} onCancel: {
  dbQueue.interrupt()
}

I'm not sure this is superior. Oh, and GRDB does not provide any interrupt methods that interrupts a specific database access:

try await withTaskCancellationHandler {
  try await dbPool.write { db in
    ...
  }
  let value = try await dbPool.read { db in
    ...
  }
} onCancel: {
  // Interrupts all writes and reads, not only the specific write and read above.
  // Other concurrent database accesses are impacted.
  dbPool.interrupt()
}

(So yeah, "Poor users, how are they supposed to reliably interrupt such a database access?" was really not a rhetorical question!)

Seconding the interrupt signal with the cancelled state solves this difficulty:

try await dbQueue.write { db in
  // It doesn't matter when cancellation happens.
  try slowWrite(db, ...) // Checks task cancellation, and can be interrupted.
  try doSomethingElse()  // Can check task cancellation.
  try slowRead(db, ...)  // Checks task cancellation, and can be interrupted.
}

Footnotes

1. It's not 100% a signal, as your failing test is teaching me 😅 After I read the documentation again and have the proper fix I'll have a better understanding :-) ↩

[sepw]

I guess the core discussion should revolve around the cancellation expectations of asynchronous Swift method calls.

You said:

SE-03024 says:

[...] The general expectation is that asynchronous functions should attempt to respond to cancellation by promptly throwing or returning.

I guess it depends on what "expectation" means in this context. If Apple were to formalize this expectation then it would be reasonable for a developer to expect that any and all long-running asynchronous methods respond "promptly" to cancellation requests. And this would, by extension, mean that GRDB's asynchronous methods be cancellable.

However, I expect that most developers don't give cancellation a passing thought, and most don't ever attempt to cancel tasks that access a database. I certainly didn't until Friday when the question first crossed my mind "I wonder how GRDB responds when I try to cancel a task that's writing to SQLite...?"

So, as I said before, GRDB finds itself caught in the gap between expectation (one way or another) and a lack of expectation (the developer doesn't care).

try await withTaskCancellationHandler {
  try await dbQueue.write { db in
   try Task.checkCancellation()
   try slowWrite(db, ...)
   try Task.checkCancellation()
   try doSomethingElse()
   try Task.checkCancellation()
   try slowRead(db, ...)
 }
} onCancel: {
  dbQueue.interrupt()
}

This certainly isn't pretty, but it's how Apple decided Swift code should check for task cancellation. I'm sure I'm not alone in having plenty of asynchronous code that doesn't look dissimilar to this! But the "prettiness" of the code isn't really a determining factor in its quality when the language designers decided that this was the expected pattern.

(Note I'm not saying Swift should have designed task cancellation differently. I think the cooperative model + leveraging the language's error/throw facility was a very reasonable decision.)

try await withTaskCancellationHandler {
  try await dbPool.write { db in
    ...
  }
  let value = try await dbPool.read { db in
    ...
  }
} onCancel: {
  // Interrupts all writes and reads, not only the specific write and read above.
  // Other concurrent database accesses are impacted.
  dbPool.interrupt()
}

I for one am very glad not to have to implement code like this! But in all honest I probably wouldn't have, even if possible (at least not unless there was a specific user-driven reason to do so). I would probably just have thought "oh well, SQL statements aren't cancellable/interruptable", shrugged and moved on, sprinkling my database access closures with try Task.checkCancellation() calls.

It's an interesting discussion, but I don't think it needs to be addressed in more detail. GRDB's implemented cancellation behavior is perfectly reasonable and requires no further justification.

[groue]

The issue seems to be related to the use of a virtual FTS5 table.

Yes. The issue disappears when we stop inserting into the FTS5 table. This probably explains why this bug report comes late: we had to wait until a user (you) would mix and match FTS5 and task cancellation.

When the task is cancelled, Task.checkCancellation() throws, the transaction wants to rollback, and this fails because the rollback statement won't even be compiled by SQLite. Quite unexpectedly, the interruption triggered by cancellation is still in effect (it has turned from a signal into a state).

Just before the rollback tries to run, the SQLite connection is indeed interrupted:

(lldb) po sqlite3_is_interrupted(sqliteConnection)
1

Asking sqlite3_next_stmt reveals that some SQLite statements are still floating around (not a problem in itself), and some of them deal with FTS5 internal tables (only when we insert in the FTS5 table):

INSERT INTO "person_text" ("id", "content") VALUES (?,?)
INSERT INTO "person" ("id", "name") VALUES (?,?)
REPLACE INTO 'main'.'person_text_docsize' VALUES(?,?)
INSERT INTO 'main'.'person_text_content' VALUES(?,?,?)
REPLACE INTO 'main'.'person_text_config' VALUES(?,?)
REPLACE INTO 'main'.'person_text_data'(id, block) VALUES(?,?)
...

My little finger tells me that:

1. FTS5 does not perform sufficient cleanup. After PersonText(...).insert, some statements are still open (not reset). Normally this does not create any problem, until...

2. Cancellation happens, and database is interrupted. The SQLite doc says:

   The sqlite3_interrupt(D) call is in effect until all currently running SQL statements on database connection D complete.

   Since the interrupt call is in effect, this means that some statements are "currently running" (i.e. not reset). It's not GRDB statements, therefore it is those internal FTS5 statements.

I'll perform more checks but I'm pretty sure this is the root cause. We found a bug in FTS5, with bad consequences on interruption.

Now I still don't know how to workaround it. I'll query the SQLite forums and perform more investigations.

---

My current advice for your application, @sepw, is to prevent GRDB from cancelling your database access. The classical technique for ignoring cancellation is to await for a new unstructured task (not ideal, I know):

// Cancelled with outer task, triggers GRDB+FTS5 bug
try await store.write { ... }

// Ignores cancellation of outer task, does not trigger the GRDB+FTS5 bug
try await Task {
  try await store.write { ... }
}.value