Command timeout not working with sqlite

[kapetan]

I can't get builder.UseSqlite("", opts => opts.CommandTimeout(60)) nor DbContext.Database.SetCommandTimeout(60) to work with sqlite. The commands always timeout after 30 seconds, no matter what I provide as command timeout value.

Steps to reproduce

Run the below code with dotnet run.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.2.6" />
  </ItemGroup>

</Project>

using System;
using System.Data;
using System.Diagnostics;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Data.Sqlite;

namespace TestEFCore {
    class Program {
        public static void Main(string[] args) {
            TestDatabaseTransactions().GetAwaiter().GetResult();
        }

        private static async Task TestDatabaseTransactions() {
            Stopwatch stopwatch = new Stopwatch();

            Task transactionTask = Task.Run(async () => {
                using (MyContext db = new MyContext())
                using (IDbContextTransaction transaction =
                        await db.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted)) {
                    while (true) {
                        Console.WriteLine("Still alive...");
                        await Task.Delay(2000);
                    }
                }
            });

            using (MyContext db = new MyContext()) {
                Console.WriteLine("About to work");

                await db.MyEntities.AddAsync(new MyEntity {
                    Value = "hello-world"
                });

                stopwatch.Start();

                try {
                    await db.SaveChangesAsync();
                } catch (Exception) {
                    stopwatch.Stop();
                    Console.WriteLine("Elapsed " + stopwatch.Elapsed.TotalSeconds);
                    throw;
                }

                Console.WriteLine("Done working");
            }

            await transactionTask;
        }

        private class MyEntity {
            public int ID { get; set; }
            public string Value { get; set; }
        }

        private class MyContext : DbContext {
            protected override void OnConfiguring(DbContextOptionsBuilder builder) {
                builder.UseSqlite("Data Source=/tmp/test.db", opts => opts.CommandTimeout(60));
            }

            public DbSet<MyEntity> MyEntities { get; set; }
        }
    }
}

Microsoft.Data.Sqlite.SqliteException: SQLite Error 5: 'database is locked'. is always throw as expected, but elapsed time is always 30 seconds no matter what the timeout is set to. The same goes when settings timeout using SetCommandTimeout.

private class MyContext : DbContext {
    public MyContext() {
        Database.SetCommandTimeout(60);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder builder) {
        builder.UseSqlite("Data Source=/tmp/test.db");
    }

    public DbSet<MyEntity> MyEntities { get; set; }
}

Terminal output.

About to work
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Still alive...
Elapsed 30.1014474

Unhandled Exception: Microsoft.Data.Sqlite.SqliteException: 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.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText)
   at Microsoft.Data.Sqlite.SqliteTransaction..ctor(SqliteConnection connection, IsolationLevel isolationLevel)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionWithNoPreconditions(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at TestEFCore.Program.TestDatabaseTransactions() in /private/tmp/TestEFCore/TestEFCore/Program.cs:line 38
   at TestEFCore.Program.Main(String[] args) in /private/tmp/TestEFCore/TestEFCore/Program.cs:line 11

Creating a SqliteConnection manually and setting DefaultTimeout seems to work.

private class MyContext : DbContext {
    public MyContext() {
        Connection.DefaultTimeout = 60;
    }

    public SqliteConnection Connection { get; } = new SqliteConnection("Data Source=/tmp/test.db");

    protected override void OnConfiguring(DbContextOptionsBuilder builder) {
        builder.UseSqlite(Connection);
    }

    public DbSet<MyEntity> MyEntities { get; set; }
}

The timeout exception is thrown after 60 seconds as expected.

Further technical details

EF Core version: 2.2.6
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET Core 2.2
Operating system: macOS 10.14.6
IDE: Visual Studio Code 1.39.2

[ajcvickers]

Note from triage: the command timeout set at the EF level should be used when we create commands internally, for example to create a transaction.

See also #18456