0

I have a SQLite database that is being used by several applications. I had always thought SQLite allowed multiple simultaneous reads, but only one app can write to it at a given time. Is this incorrect?

The layout is that I have a Java Application that is writing the DB on a continuous basis (1 transaction per second with multiple inserts in that transaction). It's a historical database so it is always storing data.

I have another application that is C# (not written by me) that reads data from this database when the user requests a report (basically a data dump). When I run this report, the Java Application will get a SQLite Exception SQLITE_BUSY "Database file is locked" exception and fail to insert data for that few seconds.

My question is, is this normal? Or should the read be able to occur while the inserts are still happening? If this is normal, I will have to add functionality to retry INSERTING if a failure occurs. Or is something in the C# code that I didn't write may be unnecessarily locking the DB for a read?

Tacitus86
  • 1,314
  • 2
  • 14
  • 36
  • 1
    The documentation is pretty comprehensive if you haven't read it already: https://www.sqlite.org/lockingv3.html – Equalsk Feb 16 '18 at 16:45
  • I don't believe SQLite was ever intended to be a multi-user database, it is a plain ol single purpose file database. I don't know what kind of problems this creates if multiple apps are using it at the same time, but I wouldn't have expected it to go well. I'd step up to PostGreSQL if that is what you need to do. – slambeth Feb 16 '18 at 16:46
  • 1
    I think you might want WAL: http://www.sqlite.org/wal.html – Equalsk Feb 16 '18 at 16:48
  • @EqualSk, There isn't much mention about READs in there to know if they should be locking or not. – Tacitus86 Feb 16 '18 at 16:52
  • @slambeth Unfortunately I do not have the option to change the DB type on this embedded system. – Tacitus86 Feb 16 '18 at 16:53
  • @Equalsk This is spot on. I researched into the writing application that I am replacing with the new Java one and it indeed did use WAL. Now I need to research how to enable WAL. Thanks you! – Tacitus86 Feb 16 '18 at 16:59

0 Answers0