title: :memory: database with shared cache is not so temporary
body: <p>The SQLite docs for in-memory databases (<a href="https://www.sqlite.org/inmemorydb.html" rel="nofollow noreferrer">https://www.sqlite.org/inmemorydb.html</a>) indicate that to share :memory: across connections, you should use URI naming and set cache=shared, like so (C API):</p>
  
  <pre><code>rc = sqlite3_open("file::memory:?cache=shared", &amp;db);
  </code></pre>
  
  <p>I tried this from Python 2.7.14, with these attributes:</p>
  
  <pre><code>&gt;&gt;&gt; sqlite3.version
  '2.6.0'
  &gt;&gt;&gt; sqlite3.apilevel
  '2.0'
  &gt;&gt;&gt; sqlite3.sqlite_version
  '3.20.1'
  </code></pre>
  
  <p>What I discovered was that the database isn't quite as transient as I would have expected. Here's a session.</p>
  
  <p>First, create a database and insert a row...</p>
  
  <pre><code>% python
  Python 2.7.14 |Anaconda, Inc.| (default, Dec  7 2017, 17:05:42) 
  [GCC 7.2.0] on linux2
  Type "help", "copyright", "credits" or "license" for more information.
  &gt;&gt;&gt; import sqlite3
  &gt;&gt;&gt; conn = sqlite3.Connection("file::memory:?cache=shared")
  &gt;&gt;&gt; curs = conn.cursor()
  &gt;&gt;&gt; curs.execute("select * from sqlite_master").fetchall()
  []
  &gt;&gt;&gt; curs.execute("create table XYZ (x int, y int, z varchar)")
  &lt;sqlite3.Cursor object at 0x7ff4b2a06110&gt;
  &gt;&gt;&gt; curs.execute("insert into XYZ values (?, ?, ?)", (1, 2, '3'))
  &lt;sqlite3.Cursor object at 0x7ff4b2a06110&gt;
  &gt;&gt;&gt; curs.execute("select * from sqlite_master").fetchall()
  [(u'table', u'XYZ', u'XYZ', 2, u'CREATE TABLE XYZ (x int, y int, z varchar)')]
  &gt;&gt;&gt; curs.execute("select * from XYZ").fetchall()
  [(1, 2, u'3')]
  &gt;&gt;&gt; conn.close()
  &gt;&gt;&gt; 
  </code></pre>
  
  <p>Look here, there's a leftover file:</p>
  
  <pre><code>% ls -l file*
  -rw-r--r-- 1 skip skip 8192 Jan 22 12:49 file::memory:?cache=shared
  </code></pre>
  
  <p>And it seems to have some bits left over from the session:</p>
  
  <pre><code>% strings -a file*
  SQLite format 3
  atableXYZXYZ
  CREATE TABLE XYZ (x int, y int, z varchar)
  </code></pre>
  
  <p>Indeed, I can open it. Although it appears to be empty (I didn't explicitly commit() in the first session, so perhaps that's why), the database is still there and accessibe:</p>
  
  <pre><code>% python
  Python 2.7.14 |Anaconda, Inc.| (default, Dec  7 2017, 17:05:42) 
  [GCC 7.2.0] on linux2
  Type "help", "copyright", "credits" or "license" for more information.
  &gt;&gt;&gt; import sqlite3
  &gt;&gt;&gt; conn = sqlite3.Connection("file::memory:?cache=shared")
  &gt;&gt;&gt; curs = conn.cursor()
  &gt;&gt;&gt; curs.execute("select * from XYZ").fetchall()
  []
  &gt;&gt;&gt; curs.execute("select * from sqlite_master").fetchall()
  [(u'table', u'XYZ', u'XYZ', 2, u'CREATE TABLE XYZ (x int, y int, z varchar)')]
  </code></pre>
  
  <p>When I first saw mention of the form "file::memory?cache=shared", I thought it looked odd. Should it instead have been something more like "file://:memory:?cache=shared"? I tried that and received an error. Still, while the documented name works, it clearly writes a file to the filesystem. I discovered this quite by accident earlier when I noticed how slow it was compared to plain old ":memory:", and how large the resulting files were. Unfortunately, those files were written to an NFS-mounted filesystem, which might explain a lot of the slowness.</p>
  
  <p>Is there a way to guarantee an in-memory database which can be accessed from multiple connections in the same process?</p>