Actors, Threading and SQLite

Fabio_Kaminski · 2021-06-17T20:10:26.348Z

I don't know how the Actors are implemented on the thread pool side, but i'm guessing they are dispatched to a thread pool with no guarantee of thread affinity/locality. but maybe if there's a concept of "shared" and "exclusive" actor that would guarantee thread affinity with the actor would make it possible to have a secure implementation for disk IO bound kind of tasks.

A shared actor would work just as it is working now, and a exclusive one would bind only to the same thread also allowing access to the TLS.

I guess such a implementation detail would make it possible to use it in scenarios of disk IO, that in my experience need's a exclusive "backend" thread allowing for the multi-threading magic to happen in the frontend consumers.

The thread pool implementation for allowing exclusive access would make it more complex and depending on the limit of threads it might have problems, but maybe leaving those limits to be configured by the developer might adapt to different use cases.

ktoso · 2021-06-18T00:10:05.575Z

Anders_Ha:

Edit: The best course IMO is not to open this rabbit hole. Performing your SQLite transactions synchronously, while still being protected by the actor's mutual exclusion, is perfectly fine. Exactly the same as how we are using SQLite today around lengthy network/IO requests.

This is sub-optimal but the present day reality -- you would not want to run long running and IO operations on actors on the default pool.

This is why we're working on Support custom executors in Swift concurrency which will enable the "make this actor run on this dedicated executor (e.g. single thread)". This is a well known and established pattern from other actor runtimes to handle such threading requirements, and does not affect the semantic model of an actor runtime -- it just affects the small detail™ of where the actor is allowed to run.

Fabio_Kaminski · 2021-06-18T03:07:15.744Z

Looking forward for custom executors, because til then i cannot use the new Swift concurrency model idioms, at least for this specific case, giving there's a need to integrate into a runtime that have also to integrate with am already present C++ runtime with its own logic, where being able to choose in which thread the execution will happen is important.

This pattern presented here with consumers/producers integrating with other systems with specific behavior might as well benefit from it.

Anders_Ha · 2021-06-19T11:35:23.592Z

Is it a strict NO to run I/O operations in the cooperative pool? Or is it more a general advice on long running ones?

Embedded databases like SQLite have come a long way to have non-blocking reads & efficient in small queries. It does feel a overkill if we advice that all things I/O of any size/duration must hop out of the cooperative pool to be executed.

Fabio_Kaminski · 2021-06-19T15:27:13.181Z

I cannot speak for them, but lets not forget that the backend threading pool implemented by actors is shared and the developers will often use them for other tasks like UI compositing for instance.

If you have a scenario where you know that it will be used only for file IO, lets say, that you implement a backend server that is specialized in doing SQlite queries, than there will be no issues.

The problem is that if this is a driver offering of a SQlite, this will run in the same process the user will use the actors for other tasks, often ones that need low latency, like UI compositing and audio for instance. In that particular case the time each IO task take to complete will become visible giving it will affect the performance of your low latency tasks.

If a handle to a SQlite have the proper locks and can be use by multiple threads, even then you would want to have a custom thread pool with "frontend" and "backend" threads that are specialized in long running tasks. With that configuration you can dispatch the IO to the backend threads without bothering the people that use your driver with unwanted lags in their final applications.

If you are not sure that the handle can work in multi-threaded mode, or you just want a more safe environment, than the best you can do is use a single-thread mode where you know only a particular thread will access your handle and from the perspective of that handle it is working in a synchronous mode, while you still can offer a multi-threaded environment from the point of view of the user, with multiple tasks consuming from the IO producer which runs is a isolated and safer environment.

With that configuration you can also use something like LMDB safely which as far as i know, unlike SQlite its not safe to be accessed by multiple threads directly.

Terje · 2021-06-22T14:10:03.395Z

Please correct me if I am wrong.

With SQLite, you have a pointer to the database file. Let’s wrap that in an actor for safety and let’s not use await within that actor’s functions for the reasons given by Anders_Ha.

SQLite also compiles SQL statements via sqlite3_prepare_v2. That function requires the database connection and can thus run on our database actor. It vends a pointer to the prepared statement and can be reused many times (if properly reset). Though multiple uses should run consecutively. So let’s stick this prepared statement pointer in an actor of it’s own.

Now we have an actor instance for the database connection that can open/close the database file and prepare statements. And for each prepared statement we have an actor instance so we bind values, reset it, and just keep track of our many different reusable queries.

But we can’t just prepare and run those queries concurrently, especially not writes. In other words both actor types (DB + all of the statements) should run on the same execution context (i.e. thread), right?

So for this usage we would have to wait for the custom executor or global actor proposal, right?

moreindirection · 2021-06-22T16:31:20.951Z

Do statement preparation and execution (and all direct SQLite interaction) on one actor (e.g. DBActor) and it should work, based on the response above from @ktoso. There's no reason to prepare your statements in a second actor, and that will quickly lead to the same database being accessed by two threads at once.

Terje · 2021-06-22T18:20:13.666Z

Which would be a global actor, right? With the database pointer and statement pointers wrapped in their own class attributed/marked with that global DBActor.

Seems to work (with the experimental concurrency compiler flag enabled).

ktoso · 2021-06-22T21:47:20.179Z

Global actors don't change anything here -- in terms of threading it is just "specifically one instance of an actor" nothing special about them.

The moment you have two actors, they may execute on 2 threads in parallel, thus breaking the "only access from single thread" requirement. So if none of those database things can be done at the same time, none such things should be "in" many actors.

It'd change if we had "pinned" (to specific thread) executors, that is true. Then many actors can be on the specific serial executor, and thus achieve mutual exclusion thanks to that, even though they are many actors.

Terje · 2021-06-30T10:22:09.757Z

I mayhaps misinterpreted the effect of a global actor. I annotated two classes with the same global actor thinking both classes would just be local to that global actor. Calling one class from the other would just be the same as if calling one function from another within an actor, i.e. not leaving the actor's island.

Not sure I am expressing myself clearly and anyways my understanding / experience with async/await has improved since last week. Not exactly sure anymore what I was thinking at the time.

ktoso · 2021-06-30T10:24:18.652Z

Terje:

Calling one class from the other would just be the same as if calling one function from another within an actor, i.e. not leaving the actor's island.

That is correct.

Though global actors are not any different than “put a bunch of stuff into the same actor”. They don’t really offer any new semantics over “in the same actor”. So the same is achievable with just “one database actor”

Terje · 2021-06-30T10:28:53.287Z

So the same is achievable with just “one database actor”

Yes, that's what I have now. And also a global actor was just a bad idea anyway if I want to open multiple files. It's an excellent exercise btw to get a grip on async/await.

ktoso · 2021-06-30T10:32:49.914Z

Correct yeah, that’s a reason one would not want to solve this using a global actor no worries, it’ll take some time getting used to those new concepts. I’m glad it seems to be going well tho!

Terje · 2021-07-04T18:26:01.656Z

Having partially non-reentrant actors would be nice in this situation I think.
The SQLite database pointer and basic functions (*_bind_*, *_column_*) inside an actor can be stuffed inside a reentrant actor.

Though that database actor shouldn’t be aware in my view of the underlying model. The model could be spread out over multiple databases, local storage for large files or even remote storage. Each of these could be their own actor with the model an actor in itself.

So if I would want to save some large file, I could for example end up with this
function on the model actor.

func add(file:…) {
await mainDB.write(..) 
await fileDB.write(…)
await remote.send(…)
}

If one of the writes fail, the other one should be rolled back. Such a function could presumably be reentrant (not there yet in my version).

But what shouldn’t be re-entrant is this function in the model actor.

func load() 
{
  let version = await mainDB.read(“select version from…”)
  if version.isOld  
  {
     await mainDB.run(“create newTable…”)
     await mainDB.write(“update version…”)
  } 
}

Every await is a suspension point during which example the add function could run. But if the database schema hasn’t been updated yet then that could fail. So the load function should be non-reentrant.

At least this is the current stage of my understanding and experience thus far. Just wanted to share.

gwendal.roue · 2021-07-05T12:22:12.644Z

Hello, I just wanted to share the concurrency patterns used in GRDB the SQLite wrapper. They have succesfully supported the development of many apps.

All writes are serialized.
```
// ONE write made of TWO steps
try dbPool.write { db in
    try db.execute(sql: "INSERT INTO player ...")
    let count = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player")!
    assert(count > 0) // Will never fail
}
```
Serializing all writes avoids the app from conflicting with itself with SQLITE_BUSY (5).

The application developer can write subsequent related statements, such as the insert and the count above, while being sure no other thread in the app can break reasonable assumptions.
All writes are wrapped in a transaction by default.
```
// ONE write made of TWO steps
try dbPool.write { db in
    try db.execute(sql: "INSERT INTO debit ...")
    try db.execute(sql: "INSERT INTO credit ...")
}
```
Explicit transaction handling is possible, but by default all writes are wrapped in a transaction. This avoids two kinds of bugs:
1. Committing partial and inconsistent database states, should one step fail.
2. Letting concurrent reads see partial updates, even if no step fails (see below).
All reads are isolated:
```
// ONE read made of TWO steps
try dbPool.read { db in
    let count1 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player")!
    let count2 = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player")!
    assert(count1 == count2) // Will never fail
}
```
By isolating all reads, the application developer can write subsequent related statements, such as the two counts above, while being sure no other thread in the app can break reasonable assumptions.

Isolation can be acheived in two ways: either by serializing all reads and writes, either by wrapping all reads in a transaction, when one wants to run parallel reads and writes on a WAL database (this is called snapshot isolation).
It is possible for perform a read from the last committed state of the database that does not block concurrent writes.

This is an advanced optimization that is more a building block for high-level tools than an application tool. Basically, when you perform a write and then a read that depends on the previous write, you may want to be able to allow concurrent writes during this dependent read. For example, when you observe the database, you may want to fetch freshly modified values without blocking concurrent writes. Such optimization requires keeping the writer locked until a reader could acquire snapshot isolation (this is usually quite fast).

In the sample code below, the count and the insertion run concurrently, maybe in parallel. Yet the assertion always succeed (even if the count happens to run after the insertion).
```
try dbPool.writeWithoutTransaction { db in
    // Delete all players
    try db.inTransaction {
        try db.execute(sql: "DELETE FROM player")
        return .commit
    }
    
    // Count players concurrently, 
    // from the last committed state of the database
    dbPool.spawnConcurrentRead { dbResult in
        do {
            let db = try dbResult.get()
            let count = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player")!
            assert(count == 0) // Will never fail
        } catch { ...   }
    }
    
    // Insert a player
    try db.execute(sql: "INSERT INTO player ...")
}
```

Absolutely none of the above behaviors are SQLite defaults:

Writes are not serialized by default (you'll just get an SQLITE_BUSY error)
Writes are not wrapped in a transaction by default (so you end up with inconsistent database states, or concurrent reads of unwanted values).
Reads are not isolated by default (they see all concurrent transactions)
Concurrent writes and reads do not synchronize by magic.
All assert above would sometimes fail.

In the above examples, you can see two kinds of APIs:

Database access methods (write, read, writeWithoutTransaction, spawnConcurrentRead...), with well-defined concurrent behavior, which provide database access to a closure argument.
Database methods (execute, fetchOne...) which must be run inside the closure argument of a database access method.

The unit of concurrency is the closure argument of a database access methods. Inside such a closure, all api calls are synchronous. It is inside such a closure that isolation guarantees apply.

Those closures are all serialized, or run concurrently, depending on the kind of database you use (WAL or non-WAL).

Non-WAL databases serialize all reads and writes.
WAL databases serialize writes, and allow concurrent read and writes.

We can build quite useful use cases on top of those building blocks:

Local reasoning: one does not have to think about the behavior of other application components. As long as you are in the closure of a database access method, you are always fully isolated.
Transactional reasoning: one can establish clear transaction boundaries.
Single source of truth: a write access guarantees full exclusivity on the database.
One can run the app on a efficient WAL database on disk, and tests and previews in an in-memory database, with the guarantee of identical behavior.
Database observation is able to not miss a single change.

In this landscape, and going back to the topic of the thread:

Reentrancy would clearly be a problem if allowed inside one database access (one read or one write). Local reasoning could no longer be possible. For example, opening transactions would not be possible as all, if anyone could interleave a commit or a rollback at any time.
I don't know how to implement "serialized writes, concurrent read and writes" with the new Swift async APIs. I'm sure it is possible, but this is not a built-in feature of actors.
I (personally) prefer thinking about the user needs, and eventually support them with Swift async features, than the opposite (expose async features and let the user guess how to build what she needs on top of those primitives). SQLite is difficult to use correctly in an asynchronous world.

gwendal.roue · 2021-07-13T08:08:36.454Z

Another data point: some people want to access several databases simultaneously: Multiple Databases Simultaneously

Current GRDB implementation allows this by maintaining a list of valid database handles in a DispatchQueue specific. Local task values are the new way to do it.

liuliu · 2021-07-13T21:22:19.184Z

Anders_Ha:

Most use SQLIte in WAL mode which has multi-reader, single-reader MVCC.

While your transaction task is suspended for the network request, latestLog() can see your uncommitted writes that are supposed to be isolated from WAL readers, until the transaction is committed.

During the suspension, the actor can be reentered with other methods that can write things that e.g. invalidate constraints/assumptions you made after the suspension point about your earlier reads.

There is a high-level API design question of how one differentiates the intent to create a nested transaction within the current running transaction, and the intent to start a separate transaction that should be deferred until the current one completes.

This is only problematic if you share the SQLite connection from writer to reader. If you use WAL mode, reader connections should be pooled while writer connections should be separated (ideally per table if you use SQLite as document storage engine like Dflat does).

(Of course, there are cases where read connection can do write, for example, triggered checkpoint operation in SQLite, and that can cause deadlocks below. Luckily, SQLite ships today can open pure read-only connection for WAL mode)

There could be deadlocks though. The only place SQLite (in WAL mode) would take a internal pthread lock is when you execute write transactions from two SQLite connections at the same time. If one actor holds one SQLite write connection, and because the executor implementation (from WWDC) capped concurrency, it is probable that you will end up with deadlocks.

In particular, imagine you have this code from two actors:

// In actor A method
db0.execute("SAVEPOINT t0")
db0.write("INSERT XXX") // This write SQL will grab an internal pthread lock
await someAsyncFunction() // Deschedule current work, potentially schedule actor B method here 
db0.write("SELECT YYY")
db0.execute("RELEASE t0") // The internal pthread lock will be released here

...

// In actor B method
db1.execute("SAVEPOINT t1")
await someOtherAsyncFunction() // Resume from actor A.
db1.write("REPLACE XXX") // This need to grab the file-level pthread lock for write access, and cannot.
db1.execute("RELEASE t1")

With limited concurrency (without aware the pthread lock), the above scenario can happen.

I will start to work on async interface for Dflat later this year, and this thread is interesting to follow. The async / await interface is not that important ATM for me because it is iOS 15 only and Dflat doesn't have a good server-side story with other more popular storage backends (such as PostgreSQL).

Chris_Lattner3 · 2021-07-25T17:26:02.976Z

I don't think that actors are going to be the ideal solution for every concurrency problem. Specifically, if you have a reference type and you want multiple concurrent multiple tasks to be able to access it, actors won't do the job (since they allow only one in at a time).

That's ok, though, because the greater Swift concurrency story has an answer: just make these things be a swift class, mark it Sendable and implement concurrency internal to the class however you'd like (atomics, locks, whatever).

Actors are intended to cover a widely used common case, while interacting with legacy code and allowing advanced cases like this to compose in. If you'd like to learn more, I'd recommend reading the SE-0302 Sendable proposal and the related discussion threads.

-Chris

liuliu · 2021-08-04T03:08:23.570Z

One thing unclear from Sendable proposal is how, for things such as SQLite that uses pthread mutexes under the hood, can interpolate well with Swift executors. Per swift-evolution/0000-custom-executors.md at custom-executors · rjmccall/swift-evolution · GitHub, default concurrent executors will have fixed width. To wrap around these C libraries like SQLite as Sendables, it must have ways to implement these pthread mutex in a way that can yield within Swift executors.

On iOS, Apple engineers ship their own version of SQLite, they probably already did this internally (i.e. have a Swift runtime friendly pthread mutex implementation). But it seems not communicated and these mixed pthread mutex with Swift async / await can be a bit trickier than I would expect.

Chris_Lattner3 · 2021-08-13T04:01:23.092Z

Hi @liuliu ,

As you say, this is more a question about custom executors and how we manually place work on them than it is Sendable. The custom executors pitch has never been run as a formal review, and I don't know the status of it, but I hope it includes something that will enable this use case.

-Chris