Fixing my SQLite Error 5: 'database is locked' error in Entity Framework
I have spent too long today trying to track down an intermittent “SQLite Error 5: 'database is locked'” error in .Net Core Entity Framework.
I have read plenty of documentation and even tried swapping to use SQL Server, as opposed to SQLite, but this just resulted in the error ‘There is already an open DataReader associated with this Connection which must be closed first.’.
So everything pointed to it being a mistake I had made.
And it was, it turns out the issue was I had the dbContext.SaveChanges() call inside a foreach loop
It was
1using (var dbContext = scope.ServiceProvider.GetRequiredService()) {
2 var itemsToQueue = dbContext.CopyOperations.Where(o => o.RequestedStartTime < DateTime.UtcNow && o.Status == OperationStatus.Queued);
3 foreach (var item in itemsToQueue) {
4 item.Status = OperationStatus.StartRequested;
5 item.StartTime = DateTime.UtcNow;
6 dbContext.SaveChanges();
7 }
8}
And it should have been
1 using (var dbContext = scope.ServiceProvider.GetRequiredService()) {
2 var itemsToQueue = dbContext.CopyOperations.Where(o => o.RequestedStartTime < DateTime.UtcNow && o.Status == OperationStatus.Queued);
3 foreach (var item in itemsToQueue) {
4 item.Status = OperationStatus.StartRequested;
5 item.StartTime = DateTime.UtcNow;
6 }
7 dbContext.SaveChanges();
8}
Once this change was made my error disappeared.