System.Data.SQLite

Hello, it seems that in-memory database (the one with "Data source=:memory:" connection string) can work only in one Open()-Close() block. When i call Open() for the second time, it creates new in-memory database and forgets the first one.

My scenario is that I'm writing IIS log analyzer which must be as quick as possible. Analyzer takes one line, analyzes it (e.g. gets browser name from user agent string) and then calls a routine to store data in database. Now it takes ages - there is some space left for optimizations but I'd rather ommit disk access at all. But I have difficulties to persist in-memory database for longer time period.

Do you have some information I should know before implementing in-memory DB? Or should I use DataSet instead (although I would lose SQL query syntax capabilities)?

Memory databases are temporary, and only exist as long as the connection is open.  If you need to make sure the database stays alive, you'll need to keep an open instance of it in your session state.  Once its closed, it's gone for good.

Robert

 

I supposed that. So maybe it's not a good idea to use in-memory SQLite db at all. I thought that it would be an elegant replacement of DataSet which doesn't understand SQL syntax.

Hi,

sorry for bringing up this old issue, but it relates to a question of mine:

does this mean, it is impossible to share a memory-database between different threads, as you need to clone the SQLiteConnection before passing it over to the second thread and this connection would see another memory database?

Best regards,
Martin

It is possible to share a connection across threads, but you must be very careful about it.  Your threads must use a mutex or critical section to block each other so that no two threads are using any connection (or any object backed to a connection) at the same time.

Robert

 

Thanks for your fast response & thanks for your clarification. The consequence of this is that there can not be any parallelization with memory-databases involved, as one has to use the same connection and strictly lock? Did I get this right?

Best regards,
Martin

Pretty much.  Two threads cannot use the same connection at the same time.  That also means that two threads cannot create or execute commands that are backed to the same connection at the same time either.  Basically you can't have two threads doing anything that is remotely associated with the connection at the same time -- you have to protect all database access on the connection with a mutex.  This includes calling any methods on an instance of a connection, setting the Connection property of a command (or causing it to be set via a constructor or whatnot), doing anything with a transaction, etc.  Datareaders are lumped in as well.  To be safe, no database objects should be touched outside a mutex.

Robert

 

Thank you very much for your clarification.

Martin