SQLite 怎么并发读取数据？

[ChengOuyang]

超哥，我这边想问个 SQLite 的问题，我在测试 SQLite 并发读取，但测试结果发现：并发读取比单线程读取还耗时（单线程 5000 条耗时 12 秒左右，2500 条 6 秒左右，而两个线程各读取 2500 条，总共耗时 18 秒左右）。而安卓那边试了一下，发现是正常的。这可能是什么原因导致的？

以下是我的测试代码：

创建数据表并插入数据

fileprivate var dbPath: String!
fileprivate var db: FMDatabase?

dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first! + "/" + "tmp.db";
if let dbFh = FMDatabase(path: dbPath) {
    dbFh.open()
    if dbFh.tableExists("test") {
        db = dbFh
    } else if dbFh.executeUpdate("create table test (a text, b text, c integer, d double, e double)", withArgumentsIn: []) {
        db = dbFh
    }
}

transaction(count: 5000)

fileprivate func transaction(count: Int = 10) {

    guard count >= 0 else {
        return
    }

    guard let dbFh = db else {
        return
    }

    if !dbFh.open() {
        return
    }

    let image = UIImage(named: "image02")!
    let imgData = UIImageJPEGRepresentation(image, 1)!

    dbFh.beginTransaction()

    for index in 0..<count {
        if !dbFh.executeUpdate("insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)", withArgumentsIn: ["index\(index)", imgData, index, index, index]) {
            print("err：", dbFh.lastErrorMessage())
            break
        }
    }

    dbFh.commit()

    if !dbFh.close() {
        return
    }
}

并发读取：

var sdb :OpaquePointer? = nil
var sdb0 :OpaquePointer? = nil

func doubleThreadRead() {

    let group = DispatchGroup()

    let flags = SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX

    group.enter()
    DispatchQueue.global().async {

        if sqlite3_open_v2(NSString(string: self.dbPath).fileSystemRepresentation, &self.sdb, flags, nil) == SQLITE_OK {
            print("open success")
        } else {
            print("open fail")
        }

        let startTime = CFAbsoluteTimeGetCurrent()

        var statement :OpaquePointer? = nil
        let sql = "select * from test limit 2500 offset 2500"
        sqlite3_prepare_v2(self.sdb!, (sql as NSString).utf8String, -1, &statement, nil)

        while sqlite3_step(statement) == SQLITE_ROW {
        }
        sqlite3_finalize(statement)

        sqlite3_close_v2(self.sdb)

        let endTime = CFAbsoluteTimeGetCurrent()

        print("db duration = ", endTime - startTime)

        group.leave()
    }

    group.enter()
    DispatchQueue.global().async {

        if sqlite3_open_v2(NSString(string: self.dbPath).fileSystemRepresentation, &self.sdb0, flags, nil) == SQLITE_OK {
            print("open success")
        } else {
            print("open fail")
        }

        let startTime = CFAbsoluteTimeGetCurrent()

        var statement :OpaquePointer? = nil
        let sql = "select * from test limit 2500"
        sqlite3_prepare_v2(self.sdb0!, (sql as NSString).utf8String, -1, &statement, nil)

        while sqlite3_step(statement) == SQLITE_ROW {
        }
        sqlite3_finalize(statement)

        sqlite3_close_v2(self.sdb0)

        let endTime = CFAbsoluteTimeGetCurrent()

        print("db0 duration = ", endTime - startTime)

        group.leave()
    }

    group.notify(queue: .main) {
        print("end")
    }
}

[netyouli]

要用事务

[netyouli]

还不支持真正并发