Can two processes access in-memory (:memory:) sqlite database concurrently?

Question

Is it possible to access database in one process, created in another? I tried:

IDLE #1

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("create table test(testcolumn)")
c.execute("insert into test values('helloooo')")
conn.commit()
conn.close()

IDLE #2

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
c.execute("select * from test")

Error:

Traceback (most recent call last):
  File "<pyshell#5>", line 1, in <module>
    q = c.execute("select * from test")
sqlite3.OperationalError: no such table: test

Each process that connects to :memory: creates its own, unique, private database, invisible to all other processes. — zwol
– zwol, Commented Mar 30, 2013 at 16:20

Martijn Pieters · Accepted Answer · 2018-09-28 06:05:36Z

45

No, they cannot ever access the same in-memory database from different processes Instead, a new connection to :memory: always creates a new database.

From the SQLite documentation:

Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

This is different from an on-disk database, where creating multiple connections with the same connection string means you are connecting to one database.

Within one process it is possible to share an in-memory database if you use the file::memory:?cache=shared URI:

conn = sqlite3.connect('file::memory:?cache=shared', uri=True)

but this is still not accessible from other another process.

edited Sep 28, 2018 at 6:05

answered Mar 30, 2013 at 16:35

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

corretge Over a year ago

even "file::memory:?cache=shared" only allows separate database connections to share the same in-memory database. But, all database connections sharing the in-memory database need to be in the same process. sqlite.org/inmemorydb.html

Martijn Pieters Over a year ago

@corretge: the file::memory:?cache=shared url was only introduced a few months before I wrote this, I see. I've added a mention, but as you say this is not usable across multiple processes.

alkasm Over a year ago

In Python 3.4+, you need to use the additional parameter uri=True to get the connect() method to open the string as a URI, instead of as a file. Otherwise it just creates a local file called file::memory:?cache=shared.

Martijn Pieters Over a year ago

@Alexander: I’d say that’s an error on my part to have omitted it. Added now, thanks for the heads up!

Drewry Pope Over a year ago

you may also connect to multiple separate memory databases, if that is what you wish. in the following code, I believe the first example is effectively equivalent to the :memory: examples.

.open file:?mode=memory&cache=shared ;  .open file:memDb1?mode=memory&cache=shared ;  .open file:memDb2?mode=memory&cache=shared ;  .open file:memDb3?mode=memory&cache=shared ;

test30 · Accepted Answer · 2014-07-28 13:13:28Z

31

of course I agree with @Martijn because doc says so, but if you are focused on unix like systems, then you can make use of shared memory:

If you create file in /dev/shm folder, all files create there are mapped directly to RAM, so you can use to access the-same database from two-different processes.

#/bin/bash
rm -f /dev/shm/test.db
time bash -c $'
FILE=/dev/shm/test.db
sqlite3 $FILE "create table if not exists tab(id int);"
sqlite3 $FILE "insert into tab values (1),(2)"
for i in 1 2 3 4; do sqlite3 $FILE "INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5"; done; #inserts at most 2'000'000 records to db.
sqlite3 $FILE "select count(*) from tab;"'

it takes that much time:

FILE=/dev/shm/test.db
real    0m0.927s
user    0m0.834s
sys 0m0.092s

for at least 2 million records, doing the same on HDD takes (this is the same command but FILE=/tmp/test.db):

FILE=/tmp/test.db
real    0m2.309s
user    0m0.871s
sys 0m0.138s

so basically this allows you accessing the same databases from different processes (without loosing r/w speed):

Here is demo demonstrating this what I am talking about:

xterm -hold -e 'sqlite3 /dev/shm/testbin "create table tab(id int); insert into tab values (42),(1337);"' &
xterm -hold -e 'sqlite3 /dev/shm/testbin "insert into tab values (43),(1338); select * from tab;"' &
;

answered Jul 28, 2014 at 13:13

test30

3,66237 silver badges26 bronze badges

1 Comment

To마SE Over a year ago

Don't use /dev/shm itself, create another instance of tmpfs, see for instance stackoverflow.com/a/42884337/846250

Collectives™ on Stack Overflow

Can two processes access in-memory (:memory:) sqlite database concurrently?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related