Configure Sqlite for multithreaded/serialized access

RyanMBritton · May 12, 2021, 8:25pm

Hi fiends,

I have a multithreaded MQ worker in a ServiceStack service which is struggling with database locks. The retry mechanism is currently ensuring that the messages are eventually successful - but it is far from ideal , not very elegant and terribly non-performant.

While considering remediation (short of swapping out databases), I happened across this article on the Sqlite site talking about multithreaded and serialized mechanisms for write access - and was wondering if this is likely to bring some relief to the issue, and if there is are mechanisms for configuring this within Ormlite?

mythz · May 12, 2021, 9:57pm

Compile time options is not possible without rebuilding the native .dll and creating new .NET packages of all Sqlite .NET .dlls to reference your custom SQLite build.

Calling the native sqlite3_open() is not possible in OrmLite which only references the managed C# ADO .NET Provider implementation of OrmLite which doesn’t directly call unsafe code that Pinvokes into the native .dll itself, the only way it could do it is by modifying the ADO .NET SQLiteConnection which doesn’t enable the configuration.

If they did support it, it would also typically be available as a connection string option which isn’t specified:

You would need to use a SQLite library that directly references the native .dll like sqlite-net:

github.com

praeclarum/sqlite-net/blob/master/src/SQLite.cs#L114


			return new NotNullConstraintViolationException (r, message, mapping, obj);
		}


		public static NotNullConstraintViolationException New (SQLiteException exception, TableMapping mapping, object obj)
		{
			return new NotNullConstraintViolationException (exception.Result, exception.Message, mapping, obj);
		}
	}


	[Flags]
	public enum SQLiteOpenFlags
	{
		ReadOnly = 1, ReadWrite = 2, Create = 4,
		NoMutex = 0x8000, FullMutex = 0x10000,
		SharedCache = 0x20000, PrivateCache = 0x40000,
		ProtectionComplete = 0x00100000,
		ProtectionCompleteUnlessOpen = 0x00200000,
		ProtectionCompleteUntilFirstUserAuthentication = 0x00300000,
		ProtectionNone = 0x00400000
	}

RyanMBritton · May 13, 2021, 9:55am

understood - thanks, Demis. I suppose that ultimately the answer here is moving to a centralized RDBMS (like Postgres). In the interim I will try to tighten up my write connections and see if I cannot bring some relief by recycling them a little better…

appreciate the help!