Efcore: "Database is locked" when multiple threads and BeginTransaction (Sqlite)

[marcova84]

When I call the method shown below from different threads (3 or more), I immediately get a "Database is locked" error:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()

Steps to reproduce

Run the following method from 3 or more threads:

```C#
public void Run()
{
while (true)
{
var cs = "Data Source=sandbox.db";

            using (var con = new SqliteConnection(cs))
            {
                con.Open();

                using (var tr = con.BeginTransaction())
                {
                    using (var cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "SELECT Value FROM SandboxItems ORDER BY SandboxItemId DESC LIMIT 1";
                        var currentValue = Convert.ToInt32(cmd.ExecuteScalar());

                        currentValue++;

                        cmd.CommandText = $"INSERT INTO SandboxItems(Name, Value) VALUES ('Sandbox item {DateTime.Now:HH: mm: ss yyyy:MM: dd}', '{currentValue}')";

                        cmd.ExecuteNonQuery();
                    }

                    tr.Commit();
                }

                con.Close();
            }

            Thread.Sleep(150);
        }
    }

```

Further technical details

EF Core version: Microsoft.EntityFrameworkCore.Sqlite (2.0.0)
Database Provider: Microsoft.Data.Sqlite.Core (2.0.0)
Operating system: Windows 10 Pro
IDE: (Visual Studio 2017)

[marcova84]

I posted it here because I encountered the problem first in EF Core with:

```C#
while (true)
{
var options = new DbContextOptionsBuilder();

            options.UseSqlite("Data Source=sandbox.db");

            using (var context = new SandboxContext(options.Options))
            {
                using (var transaction = context.Database.BeginTransaction())
                {

```

[bricelam]

Does increasing the command timeout help? Microsoft.Data.Sqlite should automatically retry when it encounters busy or locked errors.

[marcova84]

Nope, when set to 30 secs it freezes exactly that long and then throws the error.

Thanks for investigating.

[bricelam]

I'll debug further to try and understand the issue a little more.

[bricelam]

Another quick thing you can try is the nightly builds. We've made a few fixes that might help this scenario.

[peppy]

Also seeing this in my project. Haven't tested nightly yet, but just as a confirmation: is it expected that EF will handle these scenarios and continue to retry within the CommandTimeout? ie. should I be looking into manually retrying in such scenarios or not?

Also it'd be great if you could reference the recent fix PR/commits that are related to this!

[tidusjar]

I am also seeing this issue when attempting to run some sort of scheduler e.g. Hangfire, Quartz.net

Did using the nightly builds help anyone?

[dwilches]

Also seeing this in my setup. Did anyone find the solution or how to at least avoid it?

[peppy]

you can avoid by not using transactions... 💯

(yes, this is seriously our current work-around for this problem)

[peppy]

This is still broken on the lastest ef-core release (as tested here.

i'm not sure how a database engine can be useful without transactions.

[mdonoughe]

Is this the right place for this? The example code reproduces the problem using Microsoft.Data.Sqlite without EntityFrameworkCore.

It looks like if you're using the default transaction locking and the lock is blocked by a transaction from another connection that the blocked connection will never become unblocked. sqlite3_step seems to error immediately every time Microsoft.Data.Sqlite retries, even after the successful writer thread has exited (it unsuccessfully retries for 30 seconds even when I configure the test loops to stop after 5 seconds).

I noticed that you can avoid the problem by opening the connection with cache mode Shared and using IsolationLevel.ReadUncommitted for transactions, but this changes the semantics and may cause problems.

I also noticed that if the transaction is opened using BEGIN IMMEDIATE; or BEGIN EXCLUSIVE; (requires modifying Microsoft.Data.Sqlite) the problem does not occur.

I've run similar code against Microsoft.Data.Sqlite 2.0.0 and 1.1.7 and gotten the same result, but I do not get the same behavior using System.Data.Sqlite unless I set the isolation level to something like ReadCommitted. It looks like the difference is that System.Data.Sqlite uses BEGIN IMMEDIATE; for the default isolation level of Serializable: https://system.data.sqlite.org/index.html/artifact/0ea44ee9b88696c3 and GetEffectiveIsolationLevel here https://system.data.sqlite.org/index.html/artifact/0893ba7a4614ae25

[SHackauf]

I have the same problem in an application I implemented. The only solution I found out at the moment was to check the thrown exception and try to manipulate the data again. That i do maximum 5 times and if than the lock is still on the database i give up.

This solution is really dirty and I hope we found a way to fix the problem inside the framework.

[bricelam]

Adding retry logic around the transaction is the correct thing for applications to do with the current design in version 2.0.0.

We'll discuss further as a team, but I think we should change this back to use BEGIN IMMEDIATE for Serializable because it's the most helpful thing for us to do from an application perspective.

[bricelam]

@ericsink feel free to comment, "I told you so." 😉

[ericsink]

(grin)

[bricelam]

We discussed as a team and decided to go back to the version 1.x behavior. We'll continue to track this issue in aspnet/Microsoft.Data.Sqlite#474 where I've written down some additional notes.

Duplicate of aspnet/Microsoft.Data.Sqlite#474

[inossidabile]

It's unclear from history if this could affect SaveChanges too since it probably uses transactions inside? I'm experiencing the same problem but I don't use transactions directly.

[bricelam]

It seems a lot less likely to occur during concurrent SaveChanges calls, but you should be able to determine if it's the same case by looking at the logs.

[nickbelling]

I encounter this issue semi-regularly when using EF Core (i.e. I don't use transactions directly, just SaveChanges()) with Microsoft.Data.Sqlite v2.0.0. Will updating to 2.0.1 fix this?

[ajcvickers]

@nickbelling This issue is tracked by aspnet/Microsoft.Data.Sqlite#474, which was fixed in the 2.0.1 version, but without a full repro its impossible to say whether this issue is the same as the issue you are seeing. You could try it, and if it still fails then file a new issue with full details.

[seanmars]

Still got locked on current version(<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.4" />)

[bricelam]

@seanmars Locking is a normal part of relational databases. You'll need to give us more information before we can determine whether you're hitting a bug in Microsoft.Data.Sqlite or EF Core or if your code is just encountering the natural limits of SQLite.

[tidusjar]

@seanmars from my experience SQLite is awful at concurrency, especially with multiple open connections.

[seanmars]

@bricelam Hi, just run the code like @marcova84 which in the first post. Using the BeginTransaction to do something and received other query in the same time.

[bricelam]

The blocked query should automatically retry for the duration of CommandTimeout. If it can't acquire a lock in that time (possible with long-running queries or heavy traffic), it will throw with database is locked.

Unlike most database which lock specific rows, SQLite locks the entire database file making it poorly suited for heavy traffic.

[cryolithic]

@bricelam is there any issue with simply calling
SQLitePCL.raw.sqlite3_config(2 /*SQLITE_CONFIG_MULTITHREAD*/);
at startup? Assuming you're disposing your contexts right after using them, you shouldn't be breaking the underlying sqlite threading rules.

From my (somewhat limited) testing, I could spin up many reader/writer threads with no issue after setting that.

Running Core 2.2 with the latest release packages for EF/ASP/SqLite etc.

[peppy]

@cryolithic it would be useful to know whether you encountered the same issues in this thread before you set the config option.

[cryolithic]

@peppy yes, with more than a couple writer threads I quickly ran into "The database is locked" exceptions.

[tidusjar]

@cryolithic So you settingSQLitePCL.raw.sqlite3_config also affected the way EF utilizes the connection?

[cryolithic]

@tidusjar as far as I can tell, yes. Digging through the source for EF core and associated sqlite packages, I see nothing that will override the startup setting.

In my previous test scenario that could cause the issue reliably, I was able to increase the writer thread count and still see no issues.

See option 2 https://sqlite.org/threadsafe.html

[peppy]

Great to know. Might be a step forward for EF to automatically set this mode.

[cryolithic]

@peppy I'd be happy with proper configuration options.

[cryolithic]

@tidusjar just clicked through your profile, I'm a big fan of Ombi!

[tidusjar]

Thanks man! Don't want to take this off topic, but send me a email!

[seriouz]

@cryolithic How do you call this in your startup.cs?

SQLitePCL.raw.sqlite3_config(2 /SQLITE_CONFIG_MULTITHREAD/);

[cryolithic]

@cryolithic How do you call this in your startup.cs?

SQLitePCL.raw.sqlite3_config(2 /_SQLITE_CONFIG_MULTITHREAD_/);

@seriouz
Near the end of Configure() in Startup.cs

        SQLitePCL.raw.sqlite3_config(2 /*SQLITE_CONFIG_MULTITHREAD*/);

Also, if you're not running the DB in WallMode, you'll want to change that.

[seriouz]

@cryolithic When i use sqlite3_config function i get a null ref error:

Exception has occurred: CLR/System.NullReferenceException
An exception of type 'System.NullReferenceException' occurred in SQLitePCLRaw.core.dll but was not handled in user code: 'Object reference not set to an instance of an object.'
   at SQLitePCL.raw.sqlite3_config(Int32 op)

Do i have to pre configure something for SQLitePCL which EF Core then later uses?

[cryolithic]

In ConfigureServices I have
services.AddDbContext<MyDbContextClass>(options =>options.UseSqlite(Configuration.GetConnectionString("FileNameForDb")));

aside from that, at least in Asp.net 2.2.1 SQLitePCLRaw.bundle_green should come along with Microsoft.EntityFrameworkCore.Sqlite.

I run context.Database.Migrate() before calling the sqlite3_config as well, but iirc that wasn't required.