Sqlite-net: SQLite.SQLiteException: (Busy) (database is locked)

[aherrick]

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?

[Weldryn]

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]

@Weldryn are there any examples showing this?

[Weldryn]

Have a look at the issues, SO

[aherrick]

@Weldryn can you elaborate?

[wub]

@aherrick I've used something similar to this in the past:

```C#
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();
    }

}
```

[aherrick]

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));
                }
            }
        }

[wub]

@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.

[Weldryn]

@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.

[aherrick]

@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.

[Weldryn]

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

[aherrick]

Originally I had it setup without usingor lockand it was still crashing on iOS.

Would love to see a basic Xamarin Forms sample app with all these best practices setup.

[leowsouza]

@aherrick did you found a Xamarin Forms sample ? Can you share the code ?

[aherrick]

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>();
                }
            }
        }
    }

[duraz0rz]

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.

[Weldryn]

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, [e-mail] 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
.

[raquelsa]

Running into the same issue. Is making use of locks the proper way of handling that for the sync API?

[NickA55]

Is there any update on this, or the "correct" way to avoid this issue? Running into now myself.

[anthcool]

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...

[JKennedy24]

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?

[duraz0rz]

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?

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.

[JKennedy24]

@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

[ericsink]

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).

[JKennedy24]

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

[ericsink]

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.

[philipag]

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.

[stefan89]

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

[JKennedy24]

@stefan89

Use the solution in this thread:

https://github.com/praeclarum/sqlite-net/issues/881#issuecomment-552865565

This is the only solution for now

[LuckyDucko]

@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

[sjlombardo]

Rather than all these complex workarounds, has anyone tried to simply set a busy_timeout for SQLite, e.g. PRAGMA busy_timeout = 2000;?

[cbs-cbt]

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

[mchasemd]

I set it to an an unlikely high 5000ms, and still does not prevent the exception (though the exception is infrequent with lots of writes and deletes in multithreaded environment):
TimeSpan ts = new TimeSpan(0, 0, 0, 5);
await connection.SetBusyTimeoutAsync(ts);

[sjlombardo]

@mchasemd - since you seem to have a reproducible scenario, could you try SetBusyTimeoutAsync along with EnableWriteAheadLoggingAsync?

[mchasemd]

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 [e-mail]
wrote:

@mchasemd https://github.com/mchasemd - since you seem to have a
reproducible scenario, could you try SetBusyTimeoutAsync along with
EnableWriteAheadLoggingAsync?

—
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?email_source=notifications&email_token=AACGI63MOQAF3FXIH2L2N4TQ2YZ7PA5CNFSM4EUMBDB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEHXPWEQ#issuecomment-569309970,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AACGI666USRMM3RQIAIE3ELQ2YZ7PANCNFSM4EUMBDBQ
.

[mchasemd]

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(sql).ConfigureAwait(false);
});

Hope that helps. I appreciate what you do.

Matt

On Fri, Dec 27, 2019 at 1:06 PM Matthew Chase [e-mail]
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 <
[email protected]> wrote:

@mchasemd https://github.com/mchasemd - since you seem to have a
reproducible scenario, could you try SetBusyTimeoutAsync along with
EnableWriteAheadLoggingAsync?

—
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?email_source=notifications&email_token=AACGI63MOQAF3FXIH2L2N4TQ2YZ7PA5CNFSM4EUMBDB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEHXPWEQ#issuecomment-569309970,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AACGI666USRMM3RQIAIE3ELQ2YZ7PANCNFSM4EUMBDBQ
.

[JKennedy24]

@sjlombardo
This is a definite issue with V1.6.292 which is a regression from the previous version (V1.5.231)

Trying both SetBusyTimeoutAsync and EnableWriteAheadLoggingAsync have no affect.

It's more reproducable on iOS in my experience.

The two potential solutions are:

1. downgrade to V1.5.231
2. use the locking code found here: https://github.com/praeclarum/sqlite-net/issues/881#issuecomment-552865565

In fact I actually did both, but technically you only need to do one

[mchasemd]

I simply had to add static to prevent a new mutex instantiation each time I
instantiated my database class, similar to my connection being a
one-and-only static :

static readonly SemaphoreSlim mutex = new SemaphoreSlim(1, 1);

My database intensive app improved from frequent "Database Locked"
exception to very infrequent, almost nonexistent, using the later
build.

On Thu, Jan 2, 2020 at 3:46 AM JKennedy24 [e-mail] wrote:

@sjlombardo https://github.com/sjlombardo
This is a definite issue with V1.6.292 which is a regression from the
previous version (V1.5.231)

Trying both SetBusyTimeoutAsync and EnableWriteAheadLoggingAsync have no
affect.

It's more reproducable on iOS in my experience.

The two potential solutions are:

1. downgrade to V1.5.231
2. use the locking code found here: #881 (comment)
   https://github.com/praeclarum/sqlite-net/issues/881#issuecomment-552865565

In fact I actually did both, but technically you only need to do one

—
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?email_source=notifications&email_token=AACGI6ZSC5KRP33ABWV5ONDQ3WSU3A5CNFSM4EUMBDB2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEH535HQ#issuecomment-570146462,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AACGI67VES6CMB2D7UTFP6TQ3WSU3ANCNFSM4EUMBDBQ
.

[praeclarum]

Hello, would someone please report back whether this is still an issue for them using 1.7-beta (it's a little unclear).

I would love to know which OS you're using and which version of this lib. Also whether you are using the Async version or the synchronous.

Sorry for the trouble!

[praeclarum]

I was able to reproduce the bug a bit better in my async stress tests. I've found that the default busy timeout that I'm setting is a bit low (0.1s) and I've increased it to 1.0s. I'm also working on decreasing how aggressively connections to the DB are closed.

[ederbond]

I've just updated it to 1.7.335 on my Xamarin.Forms, Xamarin.iOS and Xamarin.Android app and it worked just fine. No more Database is locked here.
I'm using the async methods.
Thanks @praeclarum for the hard work on a proper fix. :)