sqlite-net: SQLite.SQLiteException: (Busy) (database is locked)
I have a Xamarin Forms app using sqlite-net.
In iOS, I sometimes see the following exceptions which are crashing the App.
DB.InsertOrReplace[T] (T item)
SQLite.SQLiteException: Busy
DB.ResetTable[T] ()
SQLite.SQLiteException: database is locked
In my .NET Standard 2.0 project I have a simple DB Class that looks like the following:
public class DB
{
private string dbPath = null;
public DB(string dbPath)
{
this.dbPath = dbPath;
using (var database = new SQLiteConnection(dbPath))
{
database.CreateTable<GenericObjectCacheDB>();
}
}
#region Generic Table Methods
public void ResetTable<T>()
{
using (var database = new SQLiteConnection(dbPath))
{
database.DropTable<T>();
database.CreateTable<T>();
}
}
public void InsertOrReplace<T>(T item)
{
using (var database = new SQLiteConnection(dbPath))
{
database.InsertOrReplace(item, typeof(T));
}
}
#endregion Generic Table Methods
}
I spin up the DB Class on App Load one time and use throughout the App. Sometimes it crashes with the above exception.
public static DB Database;
public App()
{
InitializeComponent();
Database = new DB(DependencyService.Get<IFileHelper>().GetLocalFilePath("DBSQLite.db3"));
MainPage = new NavPage(new Page());
}
Am I not correctly creating the DB or handling/disposing the instance properly?
Is there some other way I need to handle this for Xamarin Forms?
About this issue
- Original URL
- State: open
- Created 6 years ago
- Reactions: 1
- Comments: 39 (7 by maintainers)
Commits related to this issue
- Async improvements for #700 * Improve StressAsync to use tasks and await * Increase default busy timeout to 1.0s * Remove the aggressive database closing (weak ref) — committed to praeclarum/sqlite-net by praeclarum 4 years ago
- Async improvements for #700 * Improve StressAsync to use tasks and await * Increase default busy timeout to 1.0s * Remove the aggressive database closing (weak ref) — committed to Reddevildragg-UPM-Forks/sqlite-net by praeclarum 4 years ago
Is there any update on this, or the “correct” way to avoid this issue? Running into now myself.
Sorry, I hit send by accident. As you can see, I did not take my notes out of this code. Secondly, my connection (m_Connection) is static in the class Database, initially set to NULL. This code is called if a database call is made and my m_Connection is set to null. I do no close this connection until the end of the app as I have found each m_Connection.Close consumes lots of memory and since I have a database intensive app, Close => 1 MB per minute of app use. Took a while to figure that out.
I can comment out my RunInMutex code pretty easily is there is something you wish to have me check as it looks like this (and I have maybe 50 of these calls with all the tables, deletes, inserts, updates, list updates, etc): await RunInMutex(async () => { await m_Connection.ExecuteScalarAsync<int>(sql).ConfigureAwait(false); });
Hope that helps. I appreciate what you do.
Matt
On Fri, Dec 27, 2019 at 1:06 PM Matthew Chase mchasemd@heartrhythm.app wrote:
Actually no longer reproducible with the SemaphoreSlim code, but this is my constructor:
m_Connection = new SQLiteAsyncConnection(m_AppPathName, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex,storeDateTimeAsTicks:true); TimeSpan ts = new TimeSpan(0, 0, 0, 5); await m_Connection.SetBusyTimeoutAsync(ts); //Set busy timeout!!!
//m_Connection = new SQLiteAsyncConnection(m_AppPathName); //The default method by which SQLite implements atomic commit and rollback is a rollback journal.Beginning 2010 - 07 - 21, //a new “Write-Ahead Log” option(hereafter referred to as “WAL”) is available. Compared to rollback journal, advantages include: // WAL is significantly faster in most scenarios. // WAL provides more concurrency as readers do not block writers and a writer does not block readers.Reading and writing can proceed concurrently. // Disk I/ O operations tends to be more sequential using WAL. // The below code only needs to be called once in the lifetime of a database // In the future, if needed, one can explicitly change out of WAL mode using a pragma such as this, by prefixing any query statement with: // PRAGMA journal_mode = DELETE; await m_Connection.EnableWriteAheadLoggingAsync(); //You only need to call this once IN THE LIFETIME OF THE DATASE
On Fri, Dec 27, 2019 at 12:16 PM Stephen Lombardo notifications@github.com wrote:
@stefan89 Brandon Minnick (@brimmick) did a great writeup on this. of particular importance is part 6, Attempt and Retry
He suggests using Polly, a library which catches exceptions, and then attempts to retry it asynchronously with an exponentially increasing backoff.
https://codetraveler.io/2019/11/26/efficiently-initializing-sqlite-database/
Polly has an initial learning curve, however the repo Brandon provides is super helpful in working it out. https://github.com/brminnick/GitTrends
Given what @duraz0rz said:
Note that the main sqlite-net nuget package takes its dependency on SQLitePCLRaw.bundle_green, which uses my e_sqlite3 builds on every platform except iOS. I tend to discourage the use of bundle_green for cross-platform projects, because it means that one of the your platforms is using a SQLite which was built differently from the others.
So one possible fix here would be to use SQLitePCLRaw.bundle_e_sqlite3 instead.
Easiest way to do this would be to use sqlite-net-base instead of sqlite-net, and then add SQLitePCLRaw.bundle_e_sqlite3 to your app project(s).
I would advise you to keep a single
SQLiteConnectionfor your app and cache it to take advantage of the type mapping caching strategy. Opening it with theCreate | ReadWrite | FullMutexflags will ensure all operations are multithread-wise serialized. Don’t forget toDisposethe connection whenever your app closes