Handling Race conditions with Sqlite Mutex api

Question

Created Nov ’20

Replies 2

Boosts 0

Views 1.6k

Participants 2

Any advanced Sqlite pro who's familiar with read write locks (mutex) using given sqlite constructs or essentially handle Read/Write Concurrency or race conditions.

Are there such constructs that can let read happen concurrently but then locks all reading threads when write is happening. (There are of course some constructs provided by iOS like serial queues etc. but I'm interested in utilities offered by sqlite)

I've one Read and one Write operation in the following example.

Q. How to implement concurrency across both read write operations so read can happen concurrently (or not if not possible) and will be blocked on write and will wait on the write to finish. Just trying to find the right way of doing things.

Code Block swift    var database: OpaquePointer! 
    init() {
        if sqlite3_open_v2(url.path, &database, SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_FULLMUTEX, nil) == SQLITE_OK {
        }
    }
    // read operation - concurrency ok
    func getDepartments() throws -> [Department]? { 
        sqlite3_mutex_enter(sqlite3_db_mutex(database))
        var statement: OpaquePointer? = nil
        let sql = "SELECT * FROM department"
        
        guard sqlite3_prepare_v2(database, sql, -1, &statement, nil) == SQLITE_OK else {
            throw NSError(domain: String(cString: sqlite3_errmsg(database)), code: 1, userInfo: nil)
        }
        
        defer {
            sqlite3_finalize(statement)
            sqlite3_mutex_leave(sqlite3_db_mutex(database))
        }
        
        var departments: [Department] = []
        while sqlite3_step(statement) == SQLITE_ROW {
            departments.append(Department(id: sqlite3_column_int64(statement, 0), name: String(cString: sqlite3_column_text(statement, 1))))
        }
        return departments
    }
    // write operation, needs lock
    func delete(_ id: Int32) throws -> Int32? { 
        let sql = "DELETE FROM department WHERE id = \(id)"
        
        guard sqlite3_exec(database, sql, nil, nil, nil) == SQLITE_OK else {
            throw NSError(domain: String(cString: sqlite3_errmsg(database)), code: 2, userInfo: nil)
        }
        
        return sqlite3_changes(database)
    }

This returns 2 -> print(sqlite3_threadsafe())

Issue: When I call two functions concurrently, it throws EXC_BAND_INSTRUCTION with the log message

[logging] BUG IN CLIENT OF libsqlite3.dylib: illegal multi-threaded access to database connection

Someone gave suggestion to use TRANSACTIONS but looking for application/sqlite level mutex. (no serial queues etc.)

Answer 1

Frameworks Engineer OP

Apple

Dec ’20

I vigorously do not recommend this approach. Instead you can get multiple concurrent read transactions and a write transaction by switching to WAL journal_mode and having 1 connection per active thread (which can be shared with a different thread or thread pool after that transaction is complete)

if you want reads to block for a write (why?) then you can use DELETE journal mode.

The Apple build of SQLite is compiled for multi-threading mode (#2). See threadsafe.html at the sqlite website.

Even if it were built for complete serialization and you could do what you wrote there, it would behave nonsensically. Without a separate db connection per actor, concurrent use would advance the statements in a byzantine fashion. Like sharing a file descriptor's seek position between threads. Each getDepartments would end up with random slices of columns and rows.

Most people who need to scale end up with a connection pool and manage it using a dispatch queue. SQLite isn't a threading library and it's facilities are not really there for your application's use so much as the library's own implementation.

Answer 2

Frameworks Engineer OP

Apple

Dec ’20

Since you asked on the Core Data forum, btw, NSPersistentStoreCoordinator manages a connection pool for you and each NSManagedObjectContext can operate independently.