Any insight on Database locking issues with Sqlite?
10/18/2023, 10:31 AM
As I've been working on the dashboard for the recurring background jobs I've run into an interesting problem. When running against Sqlite database the database locks up after a while. This does NOT happen when running against SQL server.
I suspect it's because SQL server is issuing row level locks for the individual records in the background jobs table so that it can have multiple transactions on the table running concurrently, while Sqlite is only issuing table-level or even database-level locks.
Has anyone (including those at HQ) got any experience dealing with these kind of issues during the migration to supporting Sqlite?
I'm going to try tracing the conflicts and seeing whether I can restructure the record access to remove some of the issues - but any patterns that have proven effective would be welcome info.