0

I am using EFCore SQLite with SignalR.

When there are many clients connect at the same time, database can not changes (insert/update) anymore, this exception throws: "Can not rollback - No transaction active"

A client connects with query param that contains its info, then:

  • Look into the database, if clientName exists then update its info. Otherwise, add new.
  • After connected, client name call GetData() that returns all clients info etc...
  • There are about 100 clients connect at the same time.

Logs:

An exception occurred in the database while iterating the results of a query.
System.InvalidOperationException: A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.

Then

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.

After that, when I try to insert or update things:

Cannot rollback - No transaction is active

Is there a workaround? This only happens on production with 50 clients+, so it's hard to debug when development.

Any help will be appreciated!

trinvh
  • 1,500
  • 2
  • 11
  • 20

1 Answers1

1

SQLite is not meant to be used as a server database. This is because only one insert/update can be performed at the same time.

It is better to use SQL Server, MySQL, Postgre or some other sql database server

http://sqlite.org/whentouse.html

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

petar.kekez
  • 204
  • 2
  • 8