SQLite Error 5: 'database is locked' during writing from two DB connections
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
### Model
```C#
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; }
}
```
### Code
```C#
// Comment this whole task and application will run without any errors.
Task.Run(() =>
{
using (var db1 = new ExampleContext())
{
db1.RootEntities.First().Child = new ChildEntity();
db1.SaveChanges();
}
});
using (var db2 = new ExampleContext())
{
var rootEntities = db2.RootEntities.OrderBy(i => i.Id);
foreach (var rootEntity in rootEntities)
{
// Wait a bit until db1.SaveChanges() above will finish execution.
Thread.Sleep(2000);
rootEntity.Child = new ChildEntity();
// Here will be an error Error: SQLite Error 5: 'database is locked'
db2.SaveChanges();
}
}
```
### Project example
https://github.com/LineSmarts/SqliteBusyError
### Steps to reproduce bug
1. Open two connections to Sqlite database.
2. Write data in first connection, and begin to read data in second connection (by IQueryable).
3. Wait until first connection will be closed.
4. Attemp to write data in second connection.
5. SQLite Error 5: 'database is locked'
### Expected behaviour
Unfortunately did not find proper description of busy_timeout logic on [sqlite.org](https://www.sqlite.org/).
Found below text on this site [r-bloggers.com](https://www.r-bloggers.com/2021/03/rsqlite-concurrency-issues-solution-included/)
> If this timeout is set to a non-zero value, then the second connection will re-try the write operation several times, until it succeeds or the timeout expires.
Based on this description I expect that code above should not get any error during `db2.SaveChanges();` execution because:
1. There is no any other connection which could lock database at that moment
2. busy_timeout is set to 30 second by default, this connection should re-try the write operation several times, and should succeed.
### Include version information
Microsoft.Data.Sqlite version: 7.0.0
Target framework: (e.g. .NET 6.0)
Operating system: Windows 10
Contributor guide
Assessment
This issue has not been assessed yet.