dotnet / dotnet/efcore

SQLite Error 5: 'database is locked' during writing from two DB connections

Open
#29,514 13 comments 8 reactions 0 assignees View on GitHub
area-adonet-sqlite customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.