I'm having an issue with a multi-user sqlite database. It's a .NET 8 app with the Entity framework core v8.0.8. The database is on a network drive so multiple people can work off it.

It works fine with one person in, but once two or three people get on, we start running into SQLite errors such as the database is locked, or database disk image is malformed. There really isn't a lot of work being done, one person might be saving a few text fields, and all of a sudden everyone else that's trying to use it starts getting errors. This also prevents other people from opening (and sometimes closing) the app. When the app hangs on close, it cannot even be killed by task manager. It sits there with 10 mbps of network activity until everyone else closes out.

I have the journal mode set to WAL, locking mode=normal, synchronous=normal, page-size=4096, and WAL auto checkpoint = 1000.

The shared drive is a little slow, but it's pretty stable and reliable, so I'm not sure what's causing this. If a sql server was an option I would absolutely use that, but it's out of my control unfortunately. SQLite is my only option at the moment. If anyone has any ideas on different Pragma options I could try, or if it's possibly an SMB setting that could help I would greatly appreciate it. I understand that sqlite over SMB isn't ideal, but I would have thought it could handle at least a few users. Thanks!

The shared drive is a little slow, but it's pretty stable and reliable, so I'm not sure what's causing this

You know SQLite doesn't 'support' networks, right? Have you read this page?

https://www.sqlite.org/wal.html

Specifically this line

"WAL does not work over a network filesystem"

In my previous job, I used SQLite over a network using system.data.sqlite.dll (not Entity Framework) and not using WAL, and never had an issue.

WAL mode is persistent, so you have to butcher a database to revert that option, but I'd try without that and see if things improve.

What version of SQLite are you using? Not that its a great issue, but you note .NET 8 and other versions, but miss out the most important.

Is this shared drive on the same network, or are you using a VPN? Why is it so slow - networks these days are gigabit affairs, so shouldn't be slow, unless someone else is utilising all the bandwidth?

We originally had the database in delete mode and had similar issues. Sorry I should have checked more, just read that WAL is better for concurrency and assumed that meant for network. Any mode in particular you would recommend for SMB? And yes I know that sqlite is not made for this. It's not my decision. I agree with you.

And I'm using the nuget package Microsoft.EntityFrameworkCore.Sqlite. That's why I mentioned 8.0.8. It's at least sqlite3 but I don't know exactly what version. One of the dependencies is SQLitePCLRaw.bundle_e_sqlite3 (>=2.1.6). I'll try to find out what it actually uses.

We do not have gigabit so that may be part of the issue with the drive slowness. Sometimes it's accessed over a VPN, sometimes it's not. The VPN is obviously worse.

Appreciate your response, and will definitely turn off WAL.