SQLite Forum

Different behavior for shared cache on OSX/Linux vs Windows

Different behavior for shared cache on OSX/Linux vs Windows

(1) By Eric Sink (ericsink) on 2021-03-05 19:41:02 [link] [source]

Summary of what we are seeing:

If you open a SQLite db with the shared cache flag and then attach the same file also with the shared cache flag, SQLite gives an error ("database is already attached"), but only on OSX or Linux, not on Windows.

The differing behavior is observed using builds of SQLite 3.34.0, but the behavior on OSX is also seen using its system SQLite at 3.28.0.

The repro code looks basically like this:

const char* databasePath = "/Users/eric/dev/cb/item407/storage.ide";

const char* query3 = "attach database 'file:///Users/eric/dev/cb/item407/storage.ide?mode=memory&cache=shared' as writecache;";

int main()
    int flags = SQLITE_OPEN_CREATE |
    sqlite3* db;
    sqlite3_stmt* stmt;

    int rc = sqlite3_open_v2(databasePath, &db, flags, NULL);

    rc = sqlite3_prepare_v2(db, query3, -1, &stmt, NULL);

    rc = sqlite3_step(stmt);
    // ON Linux or OSX, this rc will be an error

    rc = sqlite3_close_v2(db);

    return 0;

Is this expected? Is it indicative of something we're doing wrong?


(2.1) By Richard Hipp (drh) on 2021-03-05 20:07:03 edited from 2.0 in reply to 1 [source]

What are you trying to do?

The "mode=memory" flag means to open the database as an in-memory database only, never reading or writing from disk. This is the same as opening the magic ":memory:" database, except that now the database has a name used internally so that multiple in-memory databases can share the same cache and hence see the same data.

Trying to open an on-disk database using "mode=memory" doesn't make sense. Where were you trying to accomplish?

I don't know what SQLite is allowing it for Windows and not for unixes. Edit: Unable to repro. I get the same result on Linux and Windows.

(3) By Eric Sink (ericsink) on 2021-03-05 20:07:14 in reply to 2.0 [link] [source]

"What are you trying to do?"

I don't really know. The context here is the dotnet/roslyn team, and there is much about their code that I do not know.

They use my C# SQLite wrapper (SQLitePCLRaw), including my SQLite builds included with same, so they reported the problem to me. I reproduced the behavior from plain C (by porting their repro project from C#) and thus determined that my code and builds are not involved, but then I was still curious why the behavior differs, so I decided to post here in the forum.

FWIW, based on what you just said in this reply, I removed "mode=memory" from the attach statement in my repro program and the "database already attached" error continues to appear.

Links for more info:

The GitHub pull request in the roslyn repo:


The corresponding issue in my repo:


(4) By Eric Sink (ericsink) on 2021-03-05 20:13:25 in reply to 2.1 [link] [source]

"Unable to repro. I get the same result on Linux and Windows."

Now that is interesting.

Which "same result" are you getting? The error? Or the lack of same?

Any other details about your repro attempt that you can share, so we can look for differences?


(5) By Richard Hipp (drh) on 2021-03-05 20:24:01 in reply to 4 [link] [source]

I get an error when the database names are the same, and no error when the names are different. Note that "same" means means exactly the same, including the "file:" prefix.

(6) By Eric Sink (ericsink) on 2021-03-05 20:35:35 in reply to 5 [link] [source]

And when you say "database name", you refer to the path of the database file?

Perhaps I'm confused, but I thought the error was referring to the attach name. So the first one, because it was opened with sqlite3_open_v2(), would get attached as "main", and the second one, because it contains the AS clause, would attached as "writecache".

(7) By Eric Sink (ericsink) on 2021-03-05 20:37:52 in reply to 3 [link] [source]

FYI, removing "mode=memory" on Windows causes it to error just like on OSX/Linux.

So, the differing behavior across platforms can be summarized simply by saying that including "mode=memory", which you said does not make sense anyway, masks what should be an error, but only on Windows.

(8) By Andrew Arnott (aarnott) on 2021-03-05 23:14:22 in reply to 7 [link] [source]

On the contrary, this is a documented technique that allows multiple sqlite connections to share one of many in-memory databases.

From https://sqlite.org/inmemorydb.html:

If two or more distinct but shareable in-memory databases are needed in a single process, then the mode=memory query parameter can be used with a URI filename to create a named in-memory database:

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

So you see we're allowed to combine a file URI with mode=memory. And it works as documented on Windows. Why doesn't it work on mac?

(9.1) By Keith Medcalf (kmedcalf) on 2021-03-06 08:06:41 edited from 9.0 in reply to 8 [link] [source]