I have read that SQLite doesn't suport well multithreading... I have the following problem and wonder if anyone could provide me some guidance.
I have a GUI application using SQLite to store its data. I have a 'Search' panel. Performing a search (scan of the database) in the background. And while it's searching the user could update the data (update the database)... In my current implementation there is: - I am using sqlite-dotnet2 (for .NET/C# from there: http://sourceforge.net/projects/sqlite-dotnet2 ) - only one connection - a global lock object, which owns the connection When you perform a data update it's surrounded by a lock (in most case, I'm not sure :S so many of them write a 2:00AM) When you perform the search, so far, I browse through a DbDataReader without lock. So it could happen that the user update the database (through the One connection), while performing a read (still through the One connection, but a different SQLCommand) at the same time. So: - is it a recipe for disaster? - is it OK? - if no would it be enough to have a lock() like that: ------ DbDataReader reader = select.ExecuteReader(): while(true) lock(globLock) { if(!reader.Read()) break; // read the data for one record here }//<= here opportunity for other thread to acquire the lock, no? maybe add a WaitEvent.. ------ Any tips?