8

I'm looking for a way to enable shared cache mode when using the System.Data.SQLite wrapper for SQLite.

I've looked through the source code for this project, and see that it's exposed internally to the assembly in UnsafeNativeMethods.cs as: 

internal static extern SQLiteErrorCode sqlite3_enable_shared_cache(
    int enable);

Unfortunately, I can't get at this method since it's internal.

Anyone have a solution for this?

Replies were most appreciated. Thanks!

FYI, when using the SQLiteConnectionStringBuilder API, enable the shared cache by:

var builder = new SQLiteConnectionStringBuilder();
...
builder.Add("cache", "shared");
Aaron Hudon
  • 5,280
  • 4
  • 53
  • 60
  • A note for others: ALL connections must have the 'cache=shared' setting in the connection string in order for them to connect to the actual shared cache, otherwise they will simply create their own. Also, for this to work for in-memory databases, a 'mode=memory' is required in the conn string as well, i.e `FullUri=file:demo?mode=memory&cache=shared;`. – bokibeg Jul 22 '21 at 08:10
  • It looks like turning shared cache on is not recommended by SQLite.org. I'm not saying not to use it, just found this interesting and relevant. "Shared-cache mode is an obsolete feature. The use of shared-cache mode is discouraged. Most use cases for shared-cache are better served by WAL mode." Quote can be found on the SQLite.org site here: https://www.sqlite.org/sharedcache.html – LoneSpawn Feb 06 '23 at 14:55

3 Answers3

6

You can enable the Shared Cache in the connection string:

var connection = new SQLiteConnection("FullUri=file:mydb.sqlite?cache=shared");
deramko
  • 2,807
  • 1
  • 18
  • 27
  • Did somebody achieve measurable performance increases with any of the solutions? Might be an error on my side, but this is not faster by any means. – Boern May 25 '18 at 06:39
4

SQLite uses PRAGMA statements to modify the database operations. These statements are specific to SQLite. PRAGMA statements can be anything from enabling Foreign Keys, changing schema versions right through to setting the Shared-Cache options (A full list of pragma commands are available here) With Pragma statements I am aware of two ways to execute them; 1) when the connection string is being instantiated or 2) Loaded as a command

1) During Instantiation

new SQLiteConnection("Data Source=c:\mydb.db;Version=3;cache=shared");

2) Separate Command Pragma statements can be executed like any normal database command sqliteConnection.Open();

var cmd = new SQLiteCommand("PRAGMA cache=shared",sqliteConnection);
cmd.ExecuteNonQuery();

Another question worth a look: SQLite SharedCache MultiThread Reads

Community
  • 1
  • 1
Helix 88
  • 701
  • 6
  • 19
0

Please also see https://www.sqlite.org/sharedcache.html - Item 1.1 suggests that shared cache should not be used anymore and WAL mode for the journal gives better results.

flohack
  • 457
  • 1
  • 4
  • 18