sqlite-net
sqlite-net copied to clipboard
praeclarum
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?
I would advise you to keep a single SQLiteConnection for your app and cache it to take advantage of the type mapping caching strategy. Opening it with the Create | ReadWrite | FullMutex flags will ensure all operations are multithread-wise serialized. Don't forget to Dispose the connection whenever your app closes
@aherrick I've used something similar to this in the past:
public class ProgressTracker : IDisposable
{
private readonly SQLiteConnection _connection;
public ProgressTracker()
{
var dbPath = Path.Combine(Environment.CurrentDirectory, "Progress.db");
_connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.FullMutex);
_connection.CreateTable<Progress>();
}
public void Dispose()
{
_connection.Close();
}
}
I ended up using lock which seems to work...
private static object collisionLock = new object();
public void Insert<T>(T item)
{
lock (collisionLock)
{
using (var database = new SQLiteConnection(dbPath))
{
database.Insert(item, typeof(T));
}
}
}
@praeclarum Do you have any insights on the correct solution to this? Seeing as SQLite comes with the specific flags necessary to solve this (FullMutex), it seems strange to "recreate" this with lock.
@aherrick You are still using the SQLiteConnection in a using block. As I said, just don't. It's very costly. You are adding pressure on the CPU and GC and avoiding optimisation by doing that.
As a good rule of thumb, cache it at the application level and use the same instance every time. Don't dispose it unless the app shuts down.
@Weldryn it's the only way I can prevent the iOS App from completely crashing when trying to access data.
Adding lock fixed the crashes. If there are other examples without using lock, I'd be happy to give it a go.
The example is: take your code as it is, remove the locks and the using blocks everywhere, remove the lines where you Dispose() the connection, create a single SQLiteConnection property in your App class to which you assign a new connection when the app starts, finally use this property everywhere. Have a look at #695 to know when to dispose the connection.
Let me know if it helped
Originally I had it setup without using or lock and it was still crashing on iOS.
Would love to see a basic Xamarin Forms sample app with all these best practices setup.
@aherrick did you found a Xamarin Forms sample ? Can you share the code ?
Never saw an official example but something like this working in my App:
public class DB
{
private string dbPath = null;
private static object collisionLock = new object();
public DB(string dbPath)
{
this.dbPath = dbPath;
CreateTable<MyTable>();
}
public MyTable GetMyTable(long id)
{
lock (collisionLock)
{
using (var database = new SQLiteConnection(dbPath))
{
return database.Table<MyTable>().Where(x => x.Id == id).SingleOrDefault();
}
}
}
private void CreateTable<T>()
{
lock (collisionLock)
{
using (var database = new SQLiteConnection(dbPath))
{
database.CreateTable<T>();
}
}
}
}
Note that SQLite on iOS is not thread-safe on a single connection. That's why you're getting the crashes without the locks. I would consider using the async API as that implements a connection pool in the code-behind.
The connection pool only implements 1 connection ad far as I can remember so there must be another mechanism that ensures thread safety.
On Wed, 13 Mar 2019, 14:35 Marc Vertido, [email protected] wrote:
Note that SQLite on iOS is not thread-safe on a single connection. That's why you're getting the crashes without the locks. I would consider using the async API as that implements a connection pool in the code-behind.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/praeclarum/sqlite-net/issues/700#issuecomment-472447205, or mute the thread https://github.com/notifications/unsubscribe-auth/AcWbQhWQ2mrmcAM7sblnZapvAj7cTSlTks5vWQydgaJpZM4SjAjD .
Running into the same issue. Is making use of locks the proper way of handling that for the sync API?
Is there any update on this, or the "correct" way to avoid this issue? Running into now myself.
So, I'm running into this now as well. Guessing despite using the SQLiteAsyncConnection variant, we still need to ensure only one instance is ever created of said object. It can perform proper serialization via locks under the hood, but they won't mean a thing if there's 2 instances trying to hit the same DB at the same time, right? I must have 2 connections attempting to hit the DB somewhere... ugh...
Note that SQLite on iOS is not thread-safe on a single connection. That's why you're getting the crashes without the locks. I would consider using the async API as that implements a connection pool in the code-behind.
I thought that the FullMutex flag allowed multiple connections? Is this not true for iOS?
I am only seeing this database locked error on iOS, not Android or UWP. Where did you find the information that says that SQLite on iOS is not thread-safe? and what is the propsed solution?
My application is multi-threaded using the fullmutex flag so does this mean I need to provide different implementations for iOS (using the lock technique) to android and uwp?
Note that SQLite on iOS is not thread-safe on a single connection. That's why you're getting the crashes without the locks. I would consider using the async API as that implements a connection pool in the code-behind.
I thought that the
FullMutexflag allowed multiple connections? Is this not true for iOS?I am only seeing this database locked error on iOS, not Android or UWP. Where did you find the information that says that SQLite on iOS is not thread-safe? and what is the propsed solution?
My application is multi-threaded using the
fullmutexflag so does this mean I need to provide different implementations for iOS (using the lock technique) to android and uwp?
SQLite does allow multiple connections for readers in FullMutex, but it only allows one connection to write at any given time. See a relevant FAQ question. Furthermore, FullMutex isn't supported by the underlying SQLite library in iOS, which SQLite.NET uses, so even if you open a DB in FullMutex, it won't work the way you expect it to (where the connection waits gracefully instead of throwing an exception). We've been bit by this on our Xamarin app.
@duraz0rz thanks for the explanation. That really helped.
What was your Xamarin solution in the end? The only solution I can see is to provide a C# lock around the writes.
Did you also encounter this database is locked problem on Android and UWP?
I am in the same situation, writing a Xamarin app for Android, iOS and UWP and have only experienced this on iOS
Given what @duraz0rz said:
Note that SQLite on iOS is not thread-safe on a single connection.
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).
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).
That makes sense.
If I'm using SqlCipher would I use SQLitePCLRaw.Bundle_e_SqlCipher instead of
SQLitePCLraw.Bundle_SqlCipher
I'm guessing SQLitePCLraw.Bundle_SqlCipher is obsolete although I can't find any information on it
bundle_sqlcipher is for SQLitePCLRaw 1.1.x
With my 2.0 release, I changed the name of my unofficial SQLCipher builds to include e_ prefix, to more clearly distinguish them from official builds available from the SQLCipher developers, so that portion of the package ID changed to bundle_e_sqlcipher.
But SQLitePCLRaw 2.0 contained breaking changes, so you have to use the right one for the sqlite-net version.
In order to use SQLitePCLRaw 2.x stuff, you have to use sqlite-net 1.7, which is still marked as beta.
Given what @duraz0rz said:
Note that SQLite on iOS is not thread-safe on a single connection.
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).
In my project SQLitePCLRaw.bundle_e_sqlite3 was causing problems and the bundle_green version worked perfectly. This is iOS 13.2, many threads but semaphore around writes.
Any updates on this? We are facing the same issue with the latest nuget package. A lot of users get SQLite.SQLiteException: database is locked
@stefan89
Use the solution in this thread:
https://github.com/praeclarum/sqlite-net/issues/881#issuecomment-552865565
This is the only solution for now
@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
Rather than all these complex workarounds, has anyone tried to simply set a busy_timeout for SQLite, e.g. PRAGMA busy_timeout = 2000;?
Rather than all these complex workarounds, has anyone tried to simply set a busy_timeout for SQLite, e.g.
PRAGMA busy_timeout = 2000;?
As suggested in this answer, I tried to define SetBusyTimeoutAsync to 2000 Milliseconds without success
