System.Data.SQLite
Ticket Change Details
Not logged in
Overview

Artifact ID: 34840c6d9cb79479fb9169ca72f8d01fa2a255a7
Ticket: 7698550f0fa6676dc9353dfc6d95a9c9d3ae8875
Database is locked
User & Date: anonymous 2018-10-04 21:51:10
Changes

  1. Change foundin to "1.0.109"
  2. Change icomment to:

    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; } } }
  3. Change login to "anonymous"
  4. Change mimetype to "text/plain"
  5. Change private_contact to "c3e20c3fd04683e5fb6438fe3caf27c67869f0c6"
  6. Change severity to "Critical"
  7. Change status to "Open"
  8. Change title to "Database is locked"
  9. Change type to "Incident"