multithreaded :memory: DB

[Mike Anderson]

I'm trying to use a :memory: sqlite3 database that is shared amongst threads. (I'm using rails activerecord). As it is now, it seems that whenever a new connection is created, it refers to a new database. Any tips? 

[Aaron Patterson]

Database connections (in general) aren't thread safe, so the Rails connection pool ensures that you only get one connection per thread.  Which explains why you're getting new connections.

Now, I'm not 100% sure about the thread safety guarantees for sqlite3.  If they are safe, you may be able to adjust the connection pool to always hand out the same connection regardless of the thread.  I suspect the sqlite3 API is thread safe (as in it won't crash), but you could definitely encounter race conditions in the Rails connection adapter.

Take these two methods as an example:

  https://github.com/rails/rails/blob/350c42c92eb4831801c34cdbe1bd317048961195/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb#L328-334

When Rails inserts a new record, it fetches the id of that record to set as the "id" value on your model by using the `last_insert_row_id` method.  So it performs two distinct steps:

1. Insert a record

2. Get the last insert id

Since the connection is assumed not to be shared among threads, there is no lock around these two steps.  If it is shared among threads there could be a race condition.

If you really need to run with the database in memory, I have two recommendations right now:

1. Run in memory, but don't use multiple threads to access the database

2. Use an in-memory FS and point sqlite3 there

I know this isn't the best solution, but I hope the info helps!

On Thu, Dec 4, 2014 at 8:21 PM, Mike Anderson <saidth...@gmail.com> wrote:

I'm trying to use a :memory: sqlite3 database that is shared amongst threads. (I'm using rails activerecord). As it is now, it seems that whenever a new connection is created, it refers to a new database. Any tips? 

--
You received this message because you are subscribed to the Google Groups "sqlite3-ruby" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlite3-ruby...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Aaron Patterson
http://tenderlovemaking.com/

[Rafael Sales]

Mike,

This will probably give what you want:

        ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'file:memdb1?mode=memory&cache=shared', pool: 10)

Reference: https://www.sqlite.org/inmemorydb.html

Notice that your SQLite must have been compiled with either THREADSAFE=1 or THREADSAFE=2 - more information on https://www.sqlite.org/compile.html#threadsafe

I've just published a post showing how you can check the THREADSAFE level on SQLite: http://theheartbit.com/.../sqlite-thread-safe-check/

[Sathish kumar]

Rafael,

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'file:memdb1?mode=memory&cache=shared', pool: 10)

- This will create a file named file:memdb1?mode=memory&cache=shared instead of in-memory DB.