Database locked issue

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
resnicp
Posts: 2
Joined: Wed 03 Jun 2020 05:30

Database locked issue

Post by resnicp » Wed 03 Jun 2020 06:53

Hi,

I am working on a multithreaded application that uses dotconnect to connect to an sqlite database.
Once in a couple of months I get the following error:
InnerException:
Type: System.Data.Entity.Core.UpdateException
Message: An error occurred while updating the entries. See the inner exception for details.
StackTrace: at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
InnerException:
Type: Devart.Data.SQLite.SQLiteException
Message: The database file is locked
database is locked
StackTrace: at Devart.Data.SQLite.a8.d(bv A_0)
at Devart.Data.SQLite.bv.b()
at Devart.Data.SQLite.af.e()
at Devart.Data.SQLite.SQLiteDataReader.c()
at Devart.Data.SQLite.SQLiteCommand.InternalExecute(CommandBehavior behavior, IDisposable statement, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

I am using a simple unit of work and repository pattern around the application. Example:
using (var unitOfWork = new UnitOfWork(new ApplicationDbContext()))
{
var testRepo = unitOfWork.Get<ITestRepository>();
//do repo and entity operations

unitOfWork.DbContext.SaveChanges();
}

At the moment I encounter a database error I log the following:
- all started unit of works .All of them seem to have the database connection closed (unitOfWork.DbContext.Database.Connection = Closed).
- all the processes that are locking the database file (only my application is using it).
I also log all the data written in the database and at the time of the error nothing seems to have been saved in parallel.
I am using dotconnect professional version 5.10.1061.
Connection string used:
new SQLiteConnectionStringBuilder
{
BinaryGUID = false,
DataSource = fullDatabasePath,
FailIfMissing = false,
DateTimeFormat = SQLiteDateFormats.ISO8601,
JournalMode = JournalMode.WAL,
Pooling = true
};

Any ideas on what could keep the database file locked?

Thank you,
Paul

Shalex
Site Admin
Posts: 9308
Joined: Thu 14 Aug 2008 12:44

Re: Database locked issue

Post by Shalex » Fri 05 Jun 2020 20:51

Try setting SQLiteConnectionStringBuilder.MinPoolSize. It is 0 by default. Increase its value.

resnicp
Posts: 2
Joined: Wed 03 Jun 2020 05:30

Re: Database locked issue

Post by resnicp » Tue 09 Jun 2020 07:45

Thank you for your reply. I have some follow up questions.
1. Do you have a recommendation for what value to set MinPoolSize and maybe even MaxPoolSize? I believe that the number of concurrent active units of work in the application can be around 10. Could a MinPoolSize = 3 and MaxPoolSize = 20 be ok?
2. I have to prove that this fix work. As mentioned I was not able to reproduce this and on the field it happens really scarce. I tried creating an unit test with multiple tasks that each have its on units of work created inside. Some tasks do reads some to writes operations . Do you have an idea on an unit test that could reproduce this issue?

Cheers,
Paul

Shalex
Site Admin
Posts: 9308
Joined: Thu 14 Aug 2008 12:44

Re: Database locked issue

Post by Shalex » Wed 01 Jul 2020 15:49

With "Pooling=true;" (default mode), conn.Close() actually doesn't close physical connection: https://www.devart.com/dotconnect/sqlit ... q.html#q54.

There are two alternative ways to solve the issue:

1) switch to "Pooling=false;"

2) with "Pooling=true;", clear pool explicitly with SQLiteConnection.ClearAllPools(true) before the operation that leads to "The database file is locked". You should implement logic in your multithreaded application to close physical connections to avoid locking database file

Post Reply