1

I want to create an in-memory SQLite DB. I would like to make two connections to this in-memory DB, one to make modifications and the other to read the DB. The modifier connection would open a transaction and continue to make modifications to the DB until a specific event occurs, at which point it would commit the transaction. The other connection would run SELECT queries reading the DB. I do not want the changes that are being made by the modifier connection to be visible to the reader connection until the modifier has committed (the specified event has occurred). I would like to isolate the reader's connection to the writer's connection.

I am writing my application in C++. I have tried opening two connections like the following:

int rc1 = sqlite3_open_v2("file:db1?mode=memory", pModifyDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_URI, NULL);
int rc2 = sqlite3_open_v2("file:db1?mode=memory", pReaderDb, SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_URI, NULL);

I have created a table, added some rows and committed the transaction to the DB using 'pModifyDb'. When I try to retrieve the values using the second connection 'pReaderDb' by calling sqlite3_exec(), I receive a return code of 1 (SQLITE_ERROR).

I've tried specifying the URI as "file:db1?mode=memory&cache=shared". I am not sure if the 'cache=shared' option would preserve isolation anymore. But that did not work either when the reader connection is trying to exec a SELECT query the return code was 6 (SQLITE_LOCKED). Maybe because the shared cache option unified both the connections under the hood?

If I remove the in-memory requirement from the URI, by using "file:db1" instead, everything works fine. I do not want to use file-based DB as I require high throughput and the size of the DB won't be very large (~10MB).

So I would like to know how to set up two isolated connections to a single SQLite in-memory DB?

Thanks in advance, kris

Shawn
  • 1,232
  • 1
  • 14
  • 44
kris_polya
  • 11
  • 1

2 Answers2

0

This is not possible with an in-memory DB.

You have to use a database file. To speed it up, put it on a RAM disk (if possible), and disable synchronous writes (PRAGMA synchronous=off) in every connection.

To allow a reader and a writer at the same time, you have to put the DB file into WAL mode.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Synchronous won't make a difference. If OP wants to read from a database at the same time as another connection is writing to it, it needs to be in WAL journal mode (Which in-memory databases don't support, so, yeah, needs to be a file-backed db). – Shawn Feb 26 '20 at 07:31
  • Thanks for the reply! I don't think I can create a RAM disk (my code is a shipping application and has to run on various platforms and I am not sure how things would pan out). For now I have cached all the modifing SQL statements and I execute them when the said event occurs. – kris_polya Feb 27 '20 at 09:46
0

This is seems possible since version 3.7.13 (2012-06-11):

Enabling shared-cache for an in-memory database allows two or more database connections in the same process to have access to the same in-memory database. An in-memory database in shared cache is automatically deleted and memory is reclaimed when the last connection to that database closes.

Docs

Zedzdead
  • 356
  • 3
  • 18