Multiple access to sqlite database using sqlite swift iOS

Question

Here's my scenario,

I have multiple request when I log in to my app, and in one of it's response contains 10,000 + records.

I am using SQLite.swift in my project.

All is working fine if the user doesn't logout or doesn't more than 1 task like searching or retrieving data from db. if any of the case happens then the app gets stuck or crashes.

I am using transactions for bulk insert, but when I try to access another same table data then the app freezes until everything is done.

I tried using multiple connections to insert to db but if another connection is using the db then its locked and app crashes

fatal error: 'try!' expression unexpectedly raised an error: database is locked: file /Library/Caches/com.apple.xbs/Sources/swiftlang/swiftlang-703.0.18.1/src/swift/stdlib/public/core/ErrorType.swift, line 54

try DataManager.con.transaction {

            for index in 0 ... (entity.count - 1) {
            try DataManager.con.run(table.insert(
                    Latitude <- entity[index]["Latitude"].string,
                    Longitude <- entity[index]["Longitude"].string
                ))
            }
        }

Here DataManager.con is a singleton object

Please help.

Perform your db transactions on the background thread or any other threads other than main thread. — Santosh
@Santosh I am doing the same, the bulk insert are in background thread, as I am using a singleton db object when I try to access another table it freezes — vinbhai4u
There can be multiple reason and you have not provided relevant information , i.e CoreData.SQLDebug logs, susceptible code block, etc. — Khundragpan
"Database is locked" is a concurrency error that SQLite.swift is supposed to prevent: you should open an issue in their Github repository. Meanwhile, have a thorough read of medium.com/@gwendal.roue/… — Gwendal Roué
Great @vinbhai4u! I still think you should warn the library author(s) of your issue, so that they know about it. — Gwendal Roué

vinbhai4u vinbhai4u · Accepted Answer · 2016-07-25T05:58:57

With sqlite.swift it was not possible to create concurrent connections.

So I moved my master data to another db and create a separate connection for it.

I also added

do{
// Start transaction
// code
// Commit transaction
}
catch {
}

to handle errors if user logout or closes the app, Transaction will help to maintain partial data saving also

Thanks to all who helped.

Multiple access to sqlite database using sqlite swift iOS

1 Answers