Sqlite Deadlock Repro

SqliteDeadlock.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
    <IsPackable>false</IsPackable>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="bogus" Version="17.0.1" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="1.1.0" />
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.3.0-preview-20170628-02" />
    <PackageReference Include="xunit" Version="2.2.0" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
  </ItemGroup>
</Project>

Tests.cs

using System;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Bogus;
using Microsoft.Data.Sqlite;
using Xunit;
using Xunit.Abstractions;

namespace SqliteDeadlock
{
    public class DbFixture
    {
        private const string filename = "tests.sqlite";
        private readonly string fullPath;

        public DbFixture()
        {
            // this.log = log;
            fullPath = Path.Combine(Environment.CurrentDirectory, filename);
            DeleteDb();
            CreateDb();
        }

        private void CreateDb()
        {
            using(var conn = Connect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
CREATE TABLE resource(id, title, data);

-- Full-text search (fts) for resources
CREATE VIRTUAL TABLE resource_fts USING fts5 (
    title, data, content = 'resource'
);

-- Weigh title matches 10 times as much as other columns
INSERT INTO resource_fts(resource_fts, rank) VALUES('rank', 'bm25(10.0)');

-- Auto-update fts index - https://sqlite.org/fts5.html#external_content_tables
CREATE TRIGGER insert_into_resource_fts AFTER INSERT ON resource BEGIN
  INSERT INTO resource_fts(rowid, title, data) VALUES (NEW.rowid, NEW.title, NEW.data);
END;
CREATE TRIGGER delete_from_resource_fts AFTER DELETE ON resource BEGIN
  INSERT INTO resource_fts(resource_fts, rowid, title, data) VALUES ('delete', OLD.rowid, OLD.title, OLD.data);
END;
CREATE TRIGGER update_resource_fts AFTER UPDATE OF title, data ON resource BEGIN
  INSERT INTO resource_fts(resource_fts, rowid, title, data) VALUES ('delete', OLD.rowid, OLD.title, OLD.data);
  INSERT INTO resource_fts(rowid, title, data) VALUES (NEW.rowid, NEW.title, NEW.data);
END;


CREATE TABLE resource2(id, title, data);

-- Full-text search (fts) for resource2
CREATE VIRTUAL TABLE resource2_fts USING fts5 (
    title, data, content = 'resource2'

-- No triggers!
);
                    ";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void DeleteDb()
        {
            File.Delete(fullPath);
        }

        public SqliteConnection Connect()
        {
            var connBuilder = new SqliteConnectionStringBuilder
            {
                DataSource = fullPath
            };
            var conn = new SqliteConnection(connBuilder.ConnectionString);
            conn.Open();
            return conn;
        }
    }

    public class ConnectionTests// : IClassFixture<DbFixture>
    {
        private readonly ITestOutputHelper log;
        private readonly DbFixture db;
        private readonly Faker faker = new Faker();

        public ConnectionTests(ITestOutputHelper output)
        {
            log = output;
            db = new DbFixture();
        }

        // Basic tests

        [Fact]
        public void TestManyInsert()
        {
            using (var conn = db.Connect())
            {
                using(var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO resource VALUES (@id,@title,@data)";
                    var id = new SqliteParameter("@id", SqliteType.Text);
                    cmd.Parameters.Add(id);
                    var title = new SqliteParameter("@title", SqliteType.Text);
                    cmd.Parameters.Add(title);
                    var data = new SqliteParameter("@data", SqliteType.Text);
                    cmd.Parameters.Add(data);
                    
                    for (int i = 1; i < 100; i++)
                    {
                        id.Value = Guid.NewGuid().ToString();
                        title.Value = faker.Lorem.Sentence();
                        data.Value = faker.Rant.Review();
                        var result = cmd.ExecuteNonQuery();
                        Assert.Equal(1, result);
                    }
                }
            }
        }

        [Fact]
        public void TestSingleInsertUpdateDelete()
        {
            var id = SingleInsert();
            SingleUpdate(id);
            SingleDelete(id);
        }

        [Fact]
        public void TestParallelInsert()
        {
            ParallelLogging(100, () => SingleInsert());
        }

        // Broken test

        [Fact]
        public void TestParallelTransaction()
        {
            // This fails even with only 2 threads
            ParallelLogging(10, TransactionInsert);
        }

        // Workaround tests

        [Fact]
        public void TestImmediateParallelTransaction()
        {
            ParallelLogging(100, ImmediateTransactionInsert);
        }

        [Fact]
        public void TestNoTriggerParallelTransaction()
        {
            ParallelLogging(100, TransactionInsertNoTrigger);
        }

        // Helpers

        private void ParallelLogging(int count, Action act)
        {
            var failures = 0;
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            Parallel.ForEach(Enumerable.Range(0, count), i =>
            {
                log.WriteLine($"{stopwatch.Elapsed} #{i} start");
                try
                {
                    act();
                    log.WriteLine($"{stopwatch.Elapsed} #{i} finish");
                }
                catch (Exception ex)
                {
                    log.WriteLine($"{stopwatch.Elapsed} #{i} FAIL {ex.GetType()}: {ex.Message}");
                    Interlocked.Increment(ref failures);
                }
            });
            stopwatch.Stop();
            log.WriteLine($"Total: {stopwatch.Elapsed}");
            Assert.Equal(0, failures);
        }

        private static long Timestamp => DateTimeOffset.Now.ToUnixTimeMilliseconds();

        private string SingleInsert()
        {
            using (var conn = db.Connect())
                return SingleInsertCmd(conn);
        }

        private string SingleInsertCmd(SqliteConnection conn)
        {
            var id = Guid.NewGuid().ToString();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO resource VALUES (@id,@title,@data)";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@title", faker.Lorem.Sentence());
                cmd.Parameters.AddWithValue("@data", faker.Rant.Review());
                var result = cmd.ExecuteNonQuery();
                Assert.Equal(1, result);
                return id;
            }
        }

        private string NoTriggersSingleInsertCmd(SqliteConnection conn)
        {
            var id = Guid.NewGuid().ToString();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO resource2 VALUES (@id,@title,@data)";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@title", faker.Lorem.Sentence());
                cmd.Parameters.AddWithValue("@data", faker.Rant.Review());
                var result = cmd.ExecuteNonQuery();
                Assert.Equal(1, result);
                cmd.CommandText = "INSERT INTO resource2_fts(rowid, title, data) SELECT rowid, title, data FROM resource2 WHERE id = @id;";
                result = cmd.ExecuteNonQuery();
                Assert.Equal(1, result);
            }
            return id;
        }

        private void SingleUpdate(string id)
        {
            using (var conn = db.Connect())
                SingleUpdateCmd(id, conn);
        }

        private void SingleUpdateCmd(string id, SqliteConnection conn)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "UPDATE resource SET title = @title, data = @data WHERE id = @id";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@title", faker.Lorem.Sentence());
                cmd.Parameters.AddWithValue("@data", faker.Rant.Review());
                var result = cmd.ExecuteNonQuery();
                Assert.Equal(1, result);
            }
        }

        private void SingleDelete(string id)
        {
            using (var conn = db.Connect())
                SingleDeleteCmd(id, conn);
        }

        private static void SingleDeleteCmd(string id, SqliteConnection conn)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "DELETE FROm resource WHERE id = @id";
                cmd.Parameters.AddWithValue("@id", id);
                var result = cmd.ExecuteNonQuery();
                Assert.Equal(1, result);
            }
        }

        private void TransactionInsert()
        {
            using (var conn = db.Connect())
            using (var tx = conn.BeginTransaction())
            {
                var id = SingleInsertCmd(conn);
                //SingleUpdateCmd(id, conn);
                tx.Commit();
            }
        }

        private void ImmediateTransactionInsert()
        {
            using (var conn = db.Connect())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "BEGIN IMMEDIATE";
                    cmd.ExecuteNonQuery();
                }
                try
                {
                    var id = SingleInsertCmd(conn);
                    // SingleUpdateCmd(id, conn);
                    
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "COMMIT";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "ROLLBACK";
                        cmd.ExecuteNonQuery();
                    }
                    throw;
                }
            }
        }

        private void TransactionInsertNoTrigger()
        {
            using (var conn = db.Connect())
            using (var tx = conn.BeginTransaction())
            {
                var id = NoTriggersSingleInsertCmd(conn);
                //SingleUpdateCmd(id, conn);
                tx.Commit();
            }
        }
    }
}