SQLite Error 6: 'database table is locked

[MichaelNielsenDK]

When cloning down an Umbraco Cloud project, and running it locally, it will start to initialize, but wil fail with this error

The source environment has thrown a Microsoft.Data.Sqlite.SqliteException with message: SQLite Error 6: 'database table is locked'.

The full error from the Umbraco log is below, and looks like it's related to the creation of countries on a Store.

There's a discussion on the forums about it here
https://our.umbraco.com/packages/website-utilities/vendr/vendr-support/110258-sqlite-does-not-support-alter-table-operations

Umbraco v 10.2.1
Vendr 3.0.3
Vendr.Persistence.Sqllite 3.0.3
Vendr.Deploy 3.0.0

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 6: 'database table is locked'.
   at Vendr.Infrastructure.Resiliency.PollyExecutionStrategyBase.Execute[TResult](Func`1 operation, Func`1 verifySucceeded)
   at Vendr.Core.VendrUnitOfWorkProvider.Execute[T](IUnitOfWorkOptions options, Func`2 action)
   at Vendr.Core.VendrUnitOfWorkProvider.Execute[T](Boolean autoComplete, Func`2 action)
   at Vendr.Core.Services.CountryService.PerformGetAllCountryStates(Guid[] ids)
   at Vendr.Core.Cache.FullDataSetEntityStatePolicyCache`2.DoGetAll(Func`2 performGetAll)
   at Vendr.Core.Cache.FullDataSetEntityStatePolicyCache`2.Get(TId id, Func`2 performGet, Func`2 performGetAll)
   at Vendr.Core.Services.CountryService.GetCountryState(Guid id)
   at Vendr.Core.Services.CountryService.GetCountry(Guid id)
   at Vendr.Core.Api.CoreVendrApi.GetCountry(Guid id)
   at Vendr.Deploy.Connectors.ServiceConnectors.VendrCountryServiceConnector.GetEntity(Guid id)
   at Vendr.Deploy.Connectors.ServiceConnectors.VendrEntityServiceConnectorBase`2.GetArtifact(GuidUdi udi)
   at Umbraco.Deploy.Infrastructure.Connectors.ServiceConnectors.ServiceConnectorBase`3.Umbraco.Cms.Core.Deploy.IServiceConnector.GetArtifact(Udi udi)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.GetSignatures(IEnumerable`1 udis, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ReviewManifest(Guid sessionId, Manifest manifest)
   at Umbraco.Deploy.Infrastructure.Environments.CurrentEnvironment.ReviewManifestAsync(Guid sessionId, Manifest manifest, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.Proceed(List`1 artifacts, Manifest manifest)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsyncSub(IWorkContext context, List`1 resume, CancellationToken token)
   at Umbraco.Deploy.Infrastructure.Work.WorkItems.DiskReadWorkItem.ExecuteAsync(IWorkContext context, CancellationToken token)

[mattbrailsford]

So a little further investigation and googling around BeginTransaction and table locks I came across this SO post https://stackoverflow.com/questions/27512393/sqlite-always-replies-with-database-table-is-locked

This points out that if you have shared cache enabled then you will receive these table lock errors when you try to start a transaction when a connection is already open. In debugging into our database connection code that reuses the Umbraco connection string, it does indeed look like cache sharing is enabled.

I'll need to speak with HQ as to why that might be necesarry as according to the SQLite docs here https://www.sqlite.org/sharedcache.html shared cache is discouraged.

[mattbrailsford]

Messaged HQ and it looks like it's an issue they are experiencing themselves in certain situations which they are set to investigate further in their next sprint. I'll update accordingly as more is known, however unfortunately there just doesn't seem to be much of a workaround at this time.

[MichaelNielsenDK]

I can't seem to find an issue on Umbraco's issue tracker, do you know if there is one? Maybe a little nudge from a Gold Partner could ensure that's is prioritized highly 🤷‍♂️😅

[TQ-Benji]

The only workaround i've found is to use SQLServer locally for the database.

[MichaelNielsenDK]

@TQ-Benji yeah me too. Export and then import a .bacpac file to your local instance of SQLServer, and use Azure Storage Explorer to download any media into the wwwroot folder.

[TQ-Benji]

@MichaelNielsenDK i needed to run the query EXEC sp_configure 'contained', 1; RECONFIGURE; on my local SQL server to be able to import the cloud .bacpac

[mattbrailsford]

Ahhh, cool. I didn't know you could do that with cloud. Glad you've found a way to work around it for the time being.

@MichaelNielsenDK I'm not sure if there is a specific issue on the issue tracker, I think they've clearly just hit it in a number of places themseleves so are aware of it. Though I agree it would be nice to have a specific location to go to in order to keep up to date on a solution.

[MichaelNielsenDK]

@TQ-Benji That sounds right, I think you need to do that, before being able to import .bacpac files

@mattbrailsford It's an ok workaround for now, to get up and running locally. 👍

[mattbrailsford]

You've got me thinking now that you are using a custom DB locally. In Vendr v3 you can provide an alternative connection string just for the Vendr DB tables so I wonder if for local dev you configure Vendr to use a sqlite database of it's own it shouldn't then have the Umbraco DB locking issue. If you then configure the staging / live environments to remove the dev Vendr connection string, it should go back to using the Umbraco DB for those environments.

Check the Vendr docs here for supplying a Vendr specific SQLite connection string (Vendr will generate the database for you similar to how Umbraco does) https://vendr.net/docs/core/3.0.0/how-to-guides/configuring-sqlite-support/

[mattbrailsford]

Bingo! That looks like it works.

So a relatively simple workaround is to modify appsettings.Development.json and add this just after the Umbraco block.

"ConnectionStrings": {
    "vendrDbDSN": "Data Source=|DataDirectory|/Vendr.sqlite.db;Mode=ReadWrite;Foreign Keys=True;Pooling=True",
    "vendrDbDSN_ProviderName": "Microsoft.Data.SQLite"
}

Then in appsettings.Production.json and appsettings.Staging.json you can set those to blank values

"ConnectionStrings": {
    "vendrDbDSN": "",
    "vendrDbDSN_ProviderName": ""
}

This should then mean locally Vendr runs from it's own SQLite DB, but on staging / prod it will go back to using the Umbraco DB which should be SQL Azure and so won't have these locking issues.

This should at least save you the headache of your dev team needing to setup a DB locally

[MichaelNielsenDK]

@mattbrailsford I didn't even think of that 🤦‍♂️, but that makes total sense, and would be a very simple workaround.

Awesome! 👍👏👌

[mattbrailsford]

Hehe, neither did I till you mentioned an alternative database connection 😁

[MichaelNielsenDK]

Just want to add that I've also tested this, and it works great.