"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:

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:

 while (true)
            {
                var options = new DbContextOptionsBuilder<SandboxContext>();

                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?