System.Data.SQLite

Multiple threads can still execute at the same time given your wrapper's design, which is unsafe and can lead to corruption.  In your code for creating a SQLiteDataReader, you aquire a readlock, execute the reader, then release the readlock and return the SQLiteDataReader to the caller.  Instead, you need to keep the readlock locked until the SQLiteDataReader is closed or you'll risk screwing up your database.  The problem is that while you are calling Read() on the reader, a writer is also writing to the database, potentially rewriting pages, moving pages and otherwise altering the database that your reader is currently trying to read.

Robert

There are times when a person just wants to pull out all his hair and give UP! Man i have tried so much and most of it is futile. I understand what you are saying is correct and also that DataReader doesnt support disconnected interface and it boils down that i have to use datasets, if i want multi threaded access on a in-memory database.

Adding more mystery to this is that when i modified the pervious code and removed all readerWriterLock checking and stopped Select (i.e. read thread) thread and only had one thread (insert) thread running, the number of records inserted per second fell and kept falling as soon as it crossed 15,000 record mark. The previous code was showing almost 10,000 insertions per second (which is unbelieveable) consistently till 2,50,000 records (more unbelieveable).

Anyways not let me ask a very direct question. Right now i am using Access (As a logging database) as database and want to replace it with SQLite with data source set as memory. And because both the insert and selects can be asynchronous, i need to ensure thread safety without comprising speed (that's why i was using DataReader its fast and very easy to use (much like recordset)).

Now the direct question is IS THERE SOME WAY TO DO THIS?

Thanks in advance.

There are a lot of ways to do this ... what are your requirements?  What is the database schema?  What is the benefit of logging to a database as opposed to logging to a flatfile?  What about logging the values to an array and periodically doing a bulk insert?  Does anything have to go to disk for permanent storage?

Robert

Benifits of logging to DB are SQL's filtering capabilities, here log file is written along with large number of flags like possible error/exception type, module name etc so it is easier (and faster) to simply write where clause and filter out the results.

Also there should be no (or min) difference between a write and subsequent read. This has been one of the trouble i have faced with access. What i mean to say is say a insert statement inserted a new now and now the row count is 2304, now the next select must read 2304. I had to use "dbIdle" in access (DAO) to force this. This has a performance penelty.

In-memory database is used for better performance as there can be many parts of program simultaneously calling same write method. There should be no bottle neck here.

(Also i was wondering (in a very positive sense) that at what time do you sleep (:-)) i mean you give a reply almost instantaneously, how do you manage all this.)

Do you need to persist this data to disk at all?  IMO you're better off getting a datatable in your read method, that way you can block the writers for the duration of it and the returned datatable is disconnected from the database.

You'll also find performance will vary widely depending on how you've laid out your database structure and indexes. 

As for sleeping :)   I get e-mail notification any time a post is made ... so if I'm able to toss back a quick reply, usually I will.  The provider is only as good as its support!

Robert