SQLite is a lightweight embedded database that has a very low resource footprint, is fast, efficient and reliable. In embedded devices, a few hundred K of memory may be sufficient. As a result, it remains one of the most common data persistence schemes for mobile device outbreaks. But even with SQLite’s maturity, we still have some programming problems, and one of the most common and difficult is concurrency.

As with other similar issues, SQLite has several different designs for concurrent processing on mobile. Let’s look at these designs through four common iOS libraries (SQLite.swift, FMDB, GRDB, and Core Data). But before we do that, we need to clarify exactly what problems SQLite has in a concurrent programming environment:

  1. Concurrent writes: At some point there may be writes to the same database, which SQLite does not allow.
  2. Operation isolation: Two consecutive database query operations may have different results, because in a concurrent environment you cannot guarantee that there will not be a write between two reads.
  3. Operation conflict: The execution timing of new and modify operations on a database in a concurrent environment is not necessarily the same as the call timing. This leads to the possibility that there may be some unexpected cases after multiple database update calls that you can’t track down.

With these problems identified, let’s take a look at how these libraries respond.

SQLite. Swift solution

SQLite. Swift uses the simplest and most brutal solution. The user only gets a connection to the database and all operations are performed under this connection string. The authors of the class library do not provide similar features for database connection pools. With this design, only one thread has access to the database at any one time. So the first concurrency problem on appeal has been solved perfectly.

But the plan fails to address the second problem. For example, we need to set the avatar of a user in the database, and perform an insert if the user exists, as follows:

let userAvatars = avatars.filter(userId == 1)
let insert = avatars.insert(userId <- 1, url <- avatarURL)
if db.scalar(userAvatars.count) == 0 {
    try db.run(insert)
}
Copy the code

The code logic doesn’t seem to have any problems or flaws at first glance, but there is a hidden problem in a concurrent environment. There is no guarantee that the same operation will not be performed anywhere after executing * try db.run(insert)*. Although such a situation is rare and there is no Crash in the database, it may be agreed that each user can only have one profile picture information when the database is set up at the beginning, which leads to business logic errors or conflicts.

Of course, this problem can be masked by the database definition, or we can explicitly deal with it through transactions:

try db.transaction {
    let userAvatars = avatars.filter(userId == 1)
    let insert = avatars.insert(userId <- 1, url <- avatarURL)
    if db.scalar(userAvatars.count) == 0 {
        try db.run(insert)
    }
}
Copy the code

Sometimes, however, developers can ignore the appeal due to issues such as time limit, and end up burying a hidden danger. For the third problem, the library doesn’t do anything about it.

FMDB scheme

FMDB, like SQlite.swift, is a serial design, but FMDB takes this one step further: the user in FMDB does not touch the database connection but instead implements database access by organizing statements in API closures.

dbQueue.inDatabase { db in
    if db.intForQuery("SELECT COUNT ...") == 0) {
        db.executeUpdate("INSERT INTO avatars ...")}}Copy the code

This approach not only solves the simultaneous write problem but also smoothly solves the operation isolation problem, which is significantly friendlier than the previous approach.

GRDB scheme

This scheme borrows from the API design in FMDB, where users implement database access by organizing statements in closures. However, the biggest difference between GRDB and the previous two is that it no longer uses a serial queue design. GRDB supports simultaneous read and write operations by multiple threads through SQLite’s own WAL mode.

Note: Writes are still sequential and WAL still needs to follow SQLite’s single-write policy

try dbPool.write { db in
    if Int.fetchOne(db, "SELECT COUNT ...") == 0) {
        try db.execute("INSERT INTO avatars ...")}}Copy the code

The most important feature of this mode is that we can execute read operations in parallel while performing database write operations. This means that database reads used to update the UI during synchronous database writes that take time for a particular thread to run will not be blocked as in the previous two scenarios. That is, write operations are transparent to read operations.

dbPool.read { db in
    // Those values are guaranteed to be equal:
    let count1 = User.fetchCount(db)
    let count2 = User.fetchCount(db)
}
Copy the code

GRDB implements read-write separation through DatabaseSnapshot, which further improves the security of multi-threaded access.

The Core Data plan

Although Apple doesn’t officially say that Core Data is a wrapper or implementation of SQLite, we do know that it actually uses SQLite as its underlying storage engine.

To address the typical SQLite concurrency issues mentioned earlier in this article, Core Data implements and maintains a set of context management logic on its own. SQLite. Swift focuses on the context of a single SQL statement during its execution. The context of interest for FMDB and GRDB is the SQL statement block in the closure. The Core Data managed context is the entire lifecycle of the NS-managed BJectContext instance, including database changes and memory changes.

This allows Core Data to deal with the third case of concurrency, where the same object is detected if it changes simultaneously in different contexts (documents). In the first three scenarios, records can be updated as long as the SQL statement does not violate the table definition and the last one always wins.

However, there are drawbacks to this design. First, the expanded context management is cumbersome, all write operations are tightly constrained and conflict handling is still tricky, and finally, the strict context management makes it difficult to write proper multithreaded code in Core Data.

conclusion

The authors of each class of libraries have their own ideas about SQLite concurrency, so there is no standard approach. If encapsulation is too simple, the demands on the user are high or there will be a lot of unexpected errors or crashes. Too complex a package can lead to poor processing flexibility. If done in a big way, SQLite can become inefficient.

Overall, the approach taken by FMDB and GRDB is a bit more secure and flexible. By the way, according to the article of wechat team, they may adopt the GRDB method, because write operation is the bottleneck in the application scenario of wechat.

The original address