Database locked exception in Mutlithread App

(1.2) By Kannan (kannang) on 2022-06-08 15:07:32 edited from 1.1 [link] [source]

Hi Team,

We are getting Database locked exception. Due to this we are not able to move ahead with SQLite. We tried all the possibility scenarios but none of them working for us. 

System.Data.SQLite.1.0.115.5
System.Data.SQLite.Core.1.0.115.5
System.Data.SQLite.EF6.1.0.115.5
System.Data.SQLite.Linq.1.0.115.5
EntityFramework.6.4.4
Dot net framework: 4.8

connectionString="DataSource=|DataDirectory|Db\Project.db;BinaryGUID=False;Pooling=True;Max Pool Size=1000;PRAGMA locking_mode = NORMAL;PRAGMAjournal_mode=WAL;Version=3;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;" providerName="System.Data.SQLite.EF6" />

2022-06-08 13:36:28,432 [20] ERROR  - System.AggregateException: One or more errors occurred. ---> System.Data.Entity.Infrastructure.CommitFailedException: An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information. ---> System.Data.SQLite.SQLiteException: database is locked
database is locked
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteTransaction.Commit()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbTransactionDispatcher.Commit(DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityTransaction.Commit()
   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()

Thanks
Kannan

(2) By Gunter Hick (gunter_hick) on 2022-06-09 09:28:14 in reply to 1.2 [link] [source]

Since you seem to have set a busy timeout in your connect string, one of your write transactions is taking longer than the timeout to process, causing a concurrent write from another thread to fail.

I assume you have one connection per thread.

(3) By Kannan (kannang) on 2022-06-09 10:46:52 in reply to 2 [link] [source]

Hi Gunter Hick,

 Thanks for your reply. But I have updated connection string as follows 

connectionString="Data Source=|DataDirectory|Db\Projec.db;BinaryGUID=False;Cache = Shared;busy_timeout = 30000;"

But still we are getting database locked exception. Basically multiple jobs concurrently try to access table. This is bottle-neck issue for us.

Could you please assist us to solve this issue. 

Thanks
Kannan

(4) By Donal Fellows (dkfellows) on 2022-06-09 13:13:40 in reply to 3 [link] [source]

You need to identify what the transaction is that is taking a long time, and find some way to shorten it (or to otherwise move it to something that isn't a problem; solutions are frequently application-specific). No amount of fiddling with the connection string is going to change the fact that long transactions that include a write are going to be troublesome as SQLite does not support concurrent write transactions, and never has. Similarly, long read transactions may also be a problem if you rely on being able to do a write transaction within a comparatively short amount of time.

Sometimes it is obvious what the problem is. Sometimes it isn't (except in retrospect once you find it). When I've run into this class of problem in the past, the fix was to stop calling bcrypt as a database function, because that's a deliberately slow operation; I moved the calls to it (mostly for handling logins) entirely outside all database transactions.

(5) By Kees Nuyt (knu) on 2022-06-09 15:24:09 in reply to 3 [source]

What is your journal_mode? If it is not wal, terminate all connections, and switch the database to wal mode journalling using the command line tool. This will allow more concurrency (one writer, many readers).

sqlite3 \path\to\database.file
.
.
PRAGMA journal_mode=WAL;

That mode is stored in the database, so you only have to issue this pragma once.

You can find details on the sqlite.org website.

-- 
Regards,
Kees Nuyt