During our local development we keep seeing errors saying that the SQLite database is locked. We have yet to figure out what triggers this. Anyone know what could be causing this or what we could try to solve it? Excerpt from the error output:

[17:34:51 ERR] Exception (fb9b415c).
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)

The full console output from this error is attached. https://cdn.discordapp.com/attachments/1229093653243494420/1229093653331443822/message.txt?ex=662e6daa&is=661bf8aa&hm=86e492a87d45b892d906cb3c6c8b68b28ebe8380687f97a2552259db3dbdc559&

Hi there - we've been experiencing something (potentially) similar in our local environment, I've posted about it in another thread but found that certain content pickers seem to trigger an API call which locks the whole site up locally. The site works fine when deployed to Umbraco cloud.

Hi, SQLite is only meant for one transaction at a time. So if you deployed a site to Azure that was using SQLite and you had multiple editors you would get errors because it can't do more than one write at a time. It might not specifically be this lock error, but I thought you should know this background information in case it helps.

That's good to know. In our case it happens during local development, so it's only one user logging in at a time. Still investigating what could be causing this.

I had an issue with this with SQLite when I added a notification handler that wrote out data from the scheduled jobs to the database. Because there were concurrent transactions running all the time on different threads it totally locked the database. SQLite doesn't have any built-in database locking at the application, table, or row level. In order to lock the db the thread obtains a full write lock on the entire file, which means no other thread can write at the same time. This is fine for simple one-person content updates, but not for multi-threaded applications. For development I would suggest swapping to SQL Server Express, or if your team are on Macs then either a networked (or cloud) SQL Server or SQL Server in a docker container (it's super easy and works really well).

Thanks @Andrew McKaskill - we can give this a try, it just begs the question why SQLite is the default development db engine for Umbraco if it's so fraught with concurrency problems..

I think because it works very quickly out of the box cross-platform and is "good-enough". The concurrency issues really only crop up when you are doing lots of multi-threaded stuff - day-to-day umbraco dev it doesn't seem to hit it (or at least hasn't for me).

I posted another thread here, but I had a simple use case of a component with more than one picker field which caused this issue 😬

But why would a component with more than one picker be "writing" to the database on multiple threads?

I don't know, that's why I'm asking in the Umbraco support thread about something that's happening in the Umbraco back office using Umbraco integrated functionality

Fair enough. I've had an issue today with the indexs being a lot slower than I though they should be and it's probably my looping code - so you never know - there's always something that can get you

Well this isn't even something interacting with code we've written, it's literally just spawning an editor view using stock Umbraco doctypes and fields. I've got a support request in so we'll see what they say.

hmm... that is interesting. Let me know what they say. I'm interested in the database locking issues more generally for work I'm doing.

If you're interested I've posted another thread with details on this specific issue I'm seeing a bit further down, it clarifies exactly what endpoint is being hit and the behaviour we're seeing

Useful tip @CodeSharePaul

Umbraco Cloud does not force the use of SQL Lite. If you do not provide any connection string for your local development, it just defaults to SQL Lite. Also for local development you should not be using a shared databases between developers, so SQL Lite is a good choice to ensure this is followed, and it is cross platform which allows you to get started without too much of an effort irrespective of if your developers using a Windows or Mac etc... With regards to the original issue, is this something that can be reproduced on a vanilla Umbraco solution without third party packages or custom code? If yes, it would be best to report it on the CMS issue tracker, that is what the support team would also suggest. Is this similar to https://github.com/umbraco/Umbraco-CMS/issues/14081 ?

As noted in my original comment, we do not use shared SQL servers for development - instead we have local SQL server instances on each of our development machines, which we are now using as a workaround for the issue we encountered.

If you have(had) a Vendr site on Cloud and try and bring that back, it can easily blow due to using SQLite. The solution of course is as @CodeSharePaul says, change the local connection string to a local DB/Docker DB. For Vendr, there is a way you can use one SQLite DB for Umbraco and another for Vendr to get around it. I just went for a SQLExpress instance in a Docker container.