Re: [sqlite] Frequent database corruptions on Windows 10
On 9/10/2016 1:16 PM, Olivier Mascia wrote: Le 10 sept. 2016 à 11:21, Alexander Täschner a écrit : since upgrading to Windows 10 I have trouble with several different C# programs I wrote, that uses System.Data.SQLite to access sqlite database files ... The programs are using multiple threads sharing one SQLiteConnection per process. I use lock statements to prevent the different threads from accessing this connection object and all derived SQLiteCommand objects simultaneously. One connection per process, shared between threads is calling for needless complications (your efforts to prevent different threads from using this connection object simultaneously, for instance). I would first refactor this a little bit, in order for each thread to use their own connection, not sharing any of these, nor any of the descendant objects from those connections. It is very simple to do and you will get rid of all that code to handle mutual exclusion. In essence, you program each thread as if they were a distinct process (regarding SQLite). It *might% fix your problem or help you find where it exactly is. And if you're using WAL mode, it will bring you some level of true read-concurrency. In your current model, you should make sure that SQLite library is set for: sqlite3_config(SQLITE_CONFIG_SERIALIZED);// SQLite enforces a mutual exclusion of threads And in the 'each thread has its own connection(s)' model which I suggest, you could downgrade that to: sqlite3_config(SQLITE_CONFIG_MULTITHREAD); // SQLite does NO attempt to isolate threads (because you do // by giving a distinct connection to each and every thread) I don't know what the default it with System.Data.SQLite. Default is Serialized for SQLite and System.Data.SQLite, and it's probably best to leave it at that even when each thread has it's own connection. More info: https://sqlite.org/threadsafe.html I'd do as Olivier suggests and refactor the code a little. Welcome to Windows 10 ;) -Bill ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Frequent database corruptions on Windows 10
> I think that the System.Data.SQLite is compiled in the multi-thread > mode and I did not find a function or option to switch it to > serialized mode, but on the other hand all the different programs > showed no problems in the last years and only started to make trouble > after my upgrade from Windows 8.1 to Windows 10 (same hardware). Is it possible that your Windows 10 installation is using different disk protocols? In particular, SMB 3.1 can be associated with the kinds of problems you are experiencing. Basic reading here: https://en.wikipedia.org/wiki/Server_Message_Block. After that...? Regards David M Bennett FACS Andl - A New Database Language - andl.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Frequent database corruptions on Windows 10
2016-09-10 19:16 GMT+02:00 Olivier Mascia :
>
> One connection per process, shared between threads is calling for needless
> complications (your efforts to prevent different threads from using this
> connection object simultaneously, for instance).
>
> I would first refactor this a little bit, in order for each thread to use
> their own connection, not sharing any of these, nor any of the descendant
> objects from those connections. It is very simple to do and you will get rid
> of all that code to handle mutual exclusion. In essence, you program each
> thread as if they were a distinct process (regarding SQLite). It *might%
> fix your problem or help you find where it exactly is. And if you're using
> WAL mode, it will bring you some level of true read-concurrency.
>
In principle you are right, but in my case I do not manage the threat
creation manually, but use the .NET thread pools via the
Parallel.ForEach or Parallel.For calls. For this kind of usage it is
much simpler to have a single class which does the database
communication wherein each function a lock is taken for the database
object, i.e.
public void Add(Collection collection)
{
lock (ArtworksSiteDatabase.DatabaseLock)
{
addNewCollectionCommand.Parameters["@uniqueCollectionIdentifier"].Value
= collection.uniqueCollectionIdentifier;
addNewCollectionCommand.Parameters["@collectionIdentifier"].Value
= collection.collectionIdentifier;
addNewCollectionCommand.Parameters["@collectionType"].Value =
collection.collectionType;
addNewCollectionCommand.Parameters["@collectionName"].Value =
collection.collectionName;
addNewCollectionCommand.ExecuteNonQuery();
}
}
Since the threads are mostly occupied by file or network I/O the time
lost by waiting for the database lock is negligible here.
I think that the System.Data.SQLite is compiled in the multi-thread
mode and I did not find a function or option to switch it to
serialized mode, but on the other hand all the different programs
showed no problems in the last years and only started to make trouble
after my upgrade from Windows 8.1 to Windows 10 (same hardware).
Kind regards,
Alexander
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Frequent database corruptions on Windows 10
> Le 10 sept. 2016 à 11:21, Alexander Täschner > a écrit : > > since upgrading to Windows 10 I have trouble with several different C# > programs I wrote, that uses System.Data.SQLite to access sqlite > database files ... > The programs are using multiple threads sharing one SQLiteConnection > per process. I use lock statements to prevent the different threads from > accessing this connection object and all derived SQLiteCommand > objects simultaneously. One connection per process, shared between threads is calling for needless complications (your efforts to prevent different threads from using this connection object simultaneously, for instance). I would first refactor this a little bit, in order for each thread to use their own connection, not sharing any of these, nor any of the descendant objects from those connections. It is very simple to do and you will get rid of all that code to handle mutual exclusion. In essence, you program each thread as if they were a distinct process (regarding SQLite). It *might% fix your problem or help you find where it exactly is. And if you're using WAL mode, it will bring you some level of true read-concurrency. In your current model, you should make sure that SQLite library is set for: sqlite3_config(SQLITE_CONFIG_SERIALIZED); // SQLite enforces a mutual exclusion of threads And in the 'each thread has its own connection(s)' model which I suggest, you could downgrade that to: sqlite3_config(SQLITE_CONFIG_MULTITHREAD);// SQLite does NO attempt to isolate threads (because you do // by giving a distinct connection to each and every thread) I don't know what the default it with System.Data.SQLite. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
