SQLite Error 5: 'database is locked' during writing from two DB connections #29514
Milestone
Comments
|
/cc @bricelam |
|
Notes for triage:
Logs: Updated code: using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading;
using System.Threading.Channels;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;
namespace EfCoreNotNullNestedOwned
{
class Program
{
static void Main(string[] args)
{
using (var db = new ExampleContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
for (int i = 0; i < 5; i++)
db.Add(new RootEntity());
db.SaveChanges();
}
// Comment this whole task and application will run without any errors.
Task.Run(() =>
{
using (var db1 = new ExampleContext())
{
db1.RootEntities.First().Child = new ChildEntity();
Thread.Sleep(500);
db1.SaveChanges();
}
Console.WriteLine("Done 1");
});
using (var db2 = new ExampleContext())
{
var rootEntities = db2.RootEntities.OrderBy(i => i.Id);
foreach (var rootEntity in rootEntities)
{
Console.WriteLine("Waiting on 2");
// Wait a bit until db1.SaveChanges() above will finish execution.
Thread.Sleep(2000);
Console.WriteLine("Starting 2");
rootEntity.Child = new ChildEntity();
// Here will be an error Error: SQLite Error 5: 'database is locked'
db2.SaveChanges();
Console.WriteLine("Done 2");
}
}
}
}
public class ExampleContext : DbContext
{
public DbSet<RootEntity> RootEntities { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.LogTo(Console.WriteLine, new[]
{
RelationalEventId.TransactionStarted,
RelationalEventId.CommandCreated,
RelationalEventId.CommandExecuting,
RelationalEventId.CommandExecuted,
RelationalEventId.TransactionCommitted,
RelationalEventId.TransactionRolledBack,
RelationalEventId.TransactionDisposed,
})
.EnableSensitiveDataLogging()
.UseSqlite($"Pooling=False;Data Source=blogging.db");
}
public class RootEntity
{
public Int32 Id { get; set; }
public ChildEntity? Child { get; set; }
}
public class ChildEntity
{
public Int32 Id { get; set; }
public String? AProperty { get; set; }
}
} |
|
I am using this extension to fix this error. It is a sad that there is no normal solution. public static class SQLiteThreadSaveExtension
{
public static ReaderWriterLockSlim DBReaderWriterLock { get; private set; } = new ReaderWriterLockSlim();
public static void SaveChangesConcurrent(this MyDBContext context)
{
try
{
DBReaderWriterLock.EnterWriteLock();
context.SaveChanges();
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task SaveChangesConcurrentAsync(this MyDBContext context)
{
try
{
DBReaderWriterLock.EnterWriteLock();
await context.SaveChangesAsync();
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static void BulkInsertConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
context.BulkInsert(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task BulkInsertConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
await context.BulkInsertAsync(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static void BulkUpdateConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
context.BulkUpdate(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task BulkUpdateConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
await context.BulkUpdateAsync(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static void BulkDeleteConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
context.BulkDelete(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task BulkDeleteConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
await context.BulkDeleteAsync(entities, bulkConfig, progress, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static int BatchUpdateConcurrent<T>(this IQueryable<T> query, Expression<Func<T, T>> updateExpression, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
return query.BatchUpdate(updateExpression, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static int BatchDeleteConcurrent<T>(this IQueryable<T> query) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
return query.BatchDelete();
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task<int> BatchUpdateConcurrentAsync<T>(this IQueryable<T> query, Expression<Func<T, T>> updateExpression, Type? type = null) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
return await query.BatchUpdateAsync(updateExpression, type);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static async Task<int> BatchDeleteConcurrentAsync<T>(this IQueryable<T> query) where T : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
return await query.BatchDeleteAsync();
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static IDbContextTransaction BeginTransactionConcurrent(this DatabaseFacade database)
{
try
{
DBReaderWriterLock.EnterWriteLock();
return database.BeginTransaction();
}
catch
{
DBReaderWriterLock.ExitWriteLock();
throw;
}
}
public static void CommitConcurrent(this IDbContextTransaction transaction)
{
try
{
transaction.Commit();
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static void TruncateConcurrent<TEntity>(this DbSet<TEntity> dbSet, MyDBContext context) where TEntity : class
{
try
{
DBReaderWriterLock.EnterWriteLock();
dbSet.Truncate(context);
}
finally
{
DBReaderWriterLock.ExitWriteLock();
}
}
public static void Truncate<TEntity>(this DbSet<TEntity> dbSet, MyDBContext context) where TEntity : class
{
if (dbSet is null)
throw new ArgumentNullException(nameof(dbSet));
var tableName = context.GetTableName(typeof(TEntity));
context.Database.ExecuteSqlRaw($"DELETE FROM \"{tableName}\"");
}
} |
AndriySvyryd
added
consider-for-next-release
and removed
consider-for-current-release
labels
AndriySvyryd
added
consider-for-current-release
and removed
consider-for-next-release
labels
|
Have anyone managed to workaround this? I think it's really critical. I also tried to use It doesn't seem a parallelism problem, it seems to me that something has not been released on the database after writing. using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
namespace EfCoreNotNullNestedOwned
{
class Program
{
static void Main(string[] args)
{
using (var db = new ExampleContext())
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
for (int i = 0; i < 5; i++)
db.Add(new RootEntity());
db.SaveChanges();
}
// Comment this whole task and application will run without any errors.
var task = Task.Run(() =>
{
using var db1 = new ExampleContext();
db1.RootEntities.First().Child = new ChildEntity();
db1.SaveChanges();
Console.WriteLine("Done 1");
});
using (var db2 = new ExampleContext())
{
var rootEntities = db2.RootEntities.OrderBy(i => i.Id);
foreach (var rootEntity in rootEntities)
{
Console.WriteLine("Waiting on 2");
// Wait a bit until db1.SaveChanges() above will finish execution.
task.Wait();
Console.WriteLine("Starting 2");
rootEntity.Child = new ChildEntity();
// Here will be an error Error: SQLite Error 5: 'database is locked'
db2.SaveChanges();
Console.WriteLine("Done 2");
}
}
}
}
public class SynchronizedEntityQueryProvider(IQueryCompiler queryCompiler) : EntityQueryProvider(queryCompiler)
{
public override object Execute(Expression expression)
{
ExampleContext.Lock.EnterReadLock();
try
{
return base.Execute(expression);
}
finally
{
ExampleContext.Lock.ExitReadLock();
}
}
public override TResult Execute<TResult>(Expression expression)
{
ExampleContext.Lock.EnterReadLock();
try
{
return base.Execute<TResult>(expression);
}
finally
{
ExampleContext.Lock.ExitReadLock();
}
}
public override TResult ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken = new CancellationToken())
{
ExampleContext.Lock.EnterReadLock();
try
{
return base.ExecuteAsync<TResult>(expression, cancellationToken);
}
finally
{
ExampleContext.Lock.ExitReadLock();
}
}
}
public class ExampleContext : DbContext
{
public static readonly ReaderWriterLockSlim Lock = new();
public DbSet<RootEntity> RootEntities { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
options
.UseSqlite($"Data Source=blogging.db")
.LogTo(Console.WriteLine, new[]
{
RelationalEventId.TransactionStarted,
RelationalEventId.CommandCreated,
RelationalEventId.CommandExecuting,
RelationalEventId.CommandExecuted,
RelationalEventId.TransactionCommitted,
RelationalEventId.TransactionRolledBack,
RelationalEventId.TransactionDisposed,
})
.EnableSensitiveDataLogging()
.ReplaceService<IAsyncQueryProvider, SynchronizedEntityQueryProvider>();
}
public override int SaveChanges()
{
Lock.EnterWriteLock();
try
{
return base.SaveChanges();
}
finally
{
Lock.ExitWriteLock();
}
}
public override void Dispose()
{
Database.CloseConnection();
base.Dispose();
}
}
public class RootEntity
{
public Int32 Id { get; set; }
public ChildEntity? Child { get; set; }
}
public class ChildEntity
{
public Int32 Id { get; set; }
public String? AProperty { get; set; }
}
}Logs: |
|
@ajcvickers @MaximMikhisor I partially understood what's happening here.
Now, I think this is a matter of "Isolation Level". I think this narrows down to how WAL works by using these So my suggestion, in general, is to never write while reading from the same db context, and maybe avoid lazy evaluation of enumerables (simply use |
|
Inside loop we have "Thread.Sleep(2000)"
Who "being able to write"? C1 or C2? |
|
@MaximMikhisor I'm saying C1 is "auto blocking itself" due to the fact it is still reading (foreach on IEnumerable) from an old checkpoint, while writing requires access to the latest checkpoint. |
|
If you comment or delete task, i.e. if only C2 will work, you will see that C2 is working ok. |
|
@MaximMikhisor exactly, that's because no one changed the checkpoint state in the mean time, so it can always write and read from the latest checkpoint. At that poi C1 is the only one doing writes and updating the checkpoint, so he knows where to write. This is the only explanation I could come up with. |
|
You a bit confusing "checkpoint" word. Checkpoint is not source of problem. I guess I found what is a source of "SQLite Error 5: 'database is locked'" error: SQLITE_BUSY_SNAPSHOT
But for some reason SQLite return "SQLite Error 5: 'database is locked'" instead of "(517)SQLITE_BUSY_SNAPSHOT" error. I think ticket could be closed. |
|
@MaximMikhisor you're right I switched words between "checkpoint" and "snapshot", but still that's the concept I wanted to explain: if someone writes while a connection is actively reading a snapshot (which I called checkpoint above) then that connection cannot write until it completes the read, because otherwise it would try to write to an old snapshot. Anyway, as we both mentioned this is a SQLite expected behavior, so we cannot do anything about it. |
6 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Model
Code
Project example
https://github.com/LineSmarts/SqliteBusyError
Steps to reproduce bug
Expected behaviour
Unfortunately did not find proper description of busy_timeout logic on sqlite.org.
Found below text on this site r-bloggers.com
Based on this description I expect that code above should not get any error during
db2.SaveChanges();execution because:Include version information
Microsoft.Data.Sqlite version: 7.0.0
Target framework: (e.g. .NET 6.0)
Operating system: Windows 10
The text was updated successfully, but these errors were encountered: