System.Data.SQLite

Hi,

I'm trying to serialize different write from different thread on the same table. 
If i read well using wal this is possible. is it? https://www.sqlite.org/wal.html

In attach there is a sample that work for a while (30 secs) until it catches an execption Database is locked.

In the example i wrote there are a lot of update that in my real code don't do. But, for some reason in my final code i catch the exception very quickly (first tentative).


Can you help me to understand the reason?

BR

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Threading;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using SQLite.CodeFirst;

namespace SqLiteParallel
{
    public class ApplicationDbContext : DbContext
    {

        public ApplicationDbContext(bool log = true)
            : base(new SQLiteConnection(new SQLiteConnectionStringBuilder()
            {
                DataSource = Path.Combine(Environment.CurrentDirectory, "mydb.db"),
                ForeignKeys = true,
                BinaryGUID = false,
                JournalMode = SQLiteJournalModeEnum.Wal,
                Pooling = true,
                SyncMode = SynchronizationModes.Full,

            } + ""), true)
        {

            if (!log) return;
            Database.Log = delegate (string s)
            {

                Console.WriteLine($"Thr: {Thread.CurrentThread.ManagedThreadId} - {s}");
            };
        }



        public virtual DbSet<MyTable> MyTables { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var sqliteConnectionInitializer = new ApplicationDbContextInitializer(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
        }

        public override int SaveChanges()
        {
            try
            {
                return base.SaveChanges();

            }
            catch (Exception e)
            {
                if (e.InnerException is SQLiteException ex)
                {
                    Console.WriteLine($"thr: {Thread.CurrentThread.ManagedThreadId} exception: {ex.Message}");
                    throw;
                }
            }

            return 1;


        }


    }

    public class ApplicationDbContextInitializer : SqliteCreateDatabaseIfNotExists<ApplicationDbContext>
    {
        public ApplicationDbContextInitializer(DbModelBuilder modelBuilder)
            : base(modelBuilder) { }

        protected override void Seed(ApplicationDbContext context)
        {
            context.Configuration.ProxyCreationEnabled = false;

        }
    }

    public class MyTable
    {
        [Required]
        public int Id { get; set; }

        [MaxLength(10), Required]
        public string Status { get; set; }

        [MaxLength(10), Required]
        public String Name { get; set; }

        public DateTime LastEvaluatingTime { get; set; }


    }


    class MyThread
    {


        public static void Worker()
        {
            using (var ctxApp = new ApplicationDbContext())
            {
                while (true)
                {
                    var d = ctxApp.MyTables.Where(r => r.Status == "wait");
                    foreach (var dataImport in d)
                    {
                        {
                            dataImport.Status = "work";
                            ctxApp.SaveChanges();
                        }

                        var a = new Thread(new ThreadWorker(dataImport.Id).Do)
                        {
                            Name = $"Importer {dataImport.Id}",
                            Priority = ThreadPriority.BelowNormal
                        };
                        a.Start();
                    }
                    //Thread.Sleep(3000);
                }
            }
        }


        public static void test(int id)
        {
            while (true)
            {
                using (var ctxApp = new ApplicationDbContext())
                {
                    IEnumerable<MyTable> m = ctxApp.MyTables.Where(r => r.Id == id);
                    if (m.Any())
                    {
                        foreach (MyTable myTable in m)
                        {
                            myTable.LastEvaluatingTime = DateTime.Now;
                            Thread.Sleep(100);
                            ctxApp.SaveChanges();

                        }
                    }
                }
            }
        }

    }

    public class ThreadWorker
    {
        public int Dimp { get; }

        public ThreadWorker(int dimp)
        {
            Dimp = dimp;
        }

        public void Do()
        {
            using (var ctxApp = new ApplicationDbContext())
            {
                int i = 0;
                var d = ctxApp.MyTables.Find(Dimp);
                for (int j = 0; j < 10; j++)
                {
                    d.LastEvaluatingTime = DateTime.Now;
                    ctxApp.SaveChanges();
                    Thread.Sleep(100);
                }

                d.Status = "wait";
                ctxApp.SaveChanges();
            }
        }
    }




    class Program
    {
        static void MyTest()
        {
            Thread b = new Thread(MyThread.Worker);
            b.Start();
            b.Join();

        }

        static void TestOnlyUpdate()
        {
            Thread b = new Thread(() => MyThread.test(1));
            b.Start();
            Thread c = new Thread(() => MyThread.test(2));
            c.Start();
            b.Join();
            c.Join();
        }


        static void Main(string[] args)
        {
            String db = Path.Combine(Environment.CurrentDirectory, "mydb.db");
            if (File.Exists(db)) File.Delete(db);

            SQLiteLog.Log += SQLiteLog_Log;
            SQLiteLog.Enabled = true;
            using (var ctxApp = new ApplicationDbContext(false))
            {
                for (int i = 0; i < 2000; i++)
                {
                    ctxApp.MyTables.Add(new MyTable()
                    {
                        Name = "a",
                        Status = "wait",
                        LastEvaluatingTime = DateTime.Now
                    });
                }
                ctxApp.SaveChanges();
            }

            TestOnlyUpdate();
             //MyTest();
        }

        private static void SQLiteLog_Log(object sender, LogEventArgs e)
        {
            int i = 0;
        }
    }
}

This type of question is best asked on the mailing list, where it can be
seen by many more people.

    [https://www.sqlite.org/support.html]

I'm not sure what your code is trying to do and I'm not familiar with the
(third-party?) components you are using.