Sqlite: appropriate use of commit

Question

In my server application(written in c#) multiple requests come simultaneously and server do both read and write operation on sqlite db.

Now problem is when Server tries to Insert anything to database at the time of some other request is Reading from database then transaction.Commit() is throwing exception database is locked, however ExecuteNonQuery() is executing successfully. So problem is only with committing to database.

We are committing after every Insert and update query. Can this problem be avoid by only committing once(keep the database in memory and commit on application close)? or is there any other way to handle this situation.

Answer 1

It seems you will need to implement a Busy Callback or Busy Timeout, sql lite has to lock the entire database when you are writing:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

Can multiple applications or multiple instances of the same application access a single database file at the same time?