Database locked error when using Begintransaction, multiple repositories, SQLite, UnitOfWork and Dapper

Question

I'm trying to implement transactions with SQLite and Dapper but I'm getting "database is locked" error.

I'm using the UnitOfWork pattern suggested here.

This is the code:

Controller.cs:

public async Task MyMethodAsync()
{
    //**injecting unitOfWork but this is the implementation**
    var unitOfWork = new UnitOfWork(new SQLiteConnection($"Data Source=TestDb;Mode=Memory;Cache=Shared"));

    using (var connection = unitOfWork.Connection)
    {
        connection.Open();
        
        unitOfWork.Begin();

        var myFirstRepository = new MyFirstRepository(unitOfWork);
        var mySecondRepository = new MySecondRepository(unitOfWork);

        try 
        {
            myFirstRepository.CreateStuff(); //Here it throws the exception "database is locked"
    
            mySecondRepository.CreateOtherStuff();
   
            unitOfWork.Commit();
        }
        catch(Exception e)
            unitOfWork.Rollback();
            throw;
        }
    }
}

MyFirstRepository.cs:

public class MyFirstRepository
{
    private IUnitOfWork _unitOfWork;    

    public MyFirstRepository(IUnitOfWork unitOfWork) 
    {
        _unitOfWork = unitOfWork;
    }

    public void CreateStuff()
    {
        using (var connection = _unitOfWork.Connection)
            {
                connection.Open();
                await connection.ExecuteAsync("INSERT INTO ...", param).ConfigureAwait(false);
            }
    }
}

MySecondRepository.cs:

Similar implementation like MyFirstRepository.cs.

Does anyone have any idea what I'm missing here?

score 0 · Answer 1 · answered Nov 26 '21 at 17:52

It looks like you're opening the connection twice - once in MyMethodAsync and again in MyFirstRepository.CreateStuff. I don't see anything in the documentation for the SQLiteConnection class about what happens if you try to open a connection twice, but maybe that's causing the issue for you? I also noticed that in the post you linked about UnitOfWork that connection.Open() is only called once as well.

Database locked error when using Begintransaction, multiple repositories, SQLite, UnitOfWork and Dapper

1 Answers1