We are using the SQLite.NET PCL in a Xamarin application.
When putting the database under pressure by doing inserts into multiple tables we are seeing BUSY exceptions being thrown.
Can anyone explain what the difference is between BUSY and LOCKED? And what causes the database to be BUSY?
Our code uses a single connection to the database created using the following code:
var connectionString = new SQLiteConnectionString(GetDefaultConnectionString(),
_databaseConfiguration.StoreTimeAsTicks);
var connectionWithLock = new SQLiteConnectionWithLock(new SQLitePlatformAndroid(), connectionString);
return new SQLiteAsyncConnection (() => { return connectionWithLock; });
So our problem turned out to be that although we had ensured within the class we'd written that it only created a single connection to the database we hadn't ensured that this class was a singleton, therefore we were still creating multiple connections to the database. Once we ensured it was a singleton then the busy errors stopped
What I've take from this is:
Locked means you have multiple threads trying to access the database, the code is inherently not thread safe.
Busy means you have a thread waiting on another thread to complete, your code is thread safe but you are seeing contention in using the database.