Correct me if I am wrong but I was under the impression that having 2 separate
connections to database (and while on subject I noticed that making connection
via ADO.NET takes significant time measured in seconds rather than in milliseconds)
will result in locking issues that I found I could not recover from, ie reconnecting
to database fails as it is still locked.
You should always be able to recover from a deadlock situation by rolling back the transaction on all but one of the connections-- thread or task-- that is attempting a commit. You can prevent deadlock situations by beginning a transaction at a particular lock level.
There is quite a lot of discussion of this in the list archives -- have a search for "exclusive and reserved".
My solution was to use single connection in a C# wrapper around ADO.NET
that would queue requests using primitive locking to avoid actual locking of database
and it can recover from "library called out of sequence" errors.
SQLite specifically does not support multithreaded execution upon a single SQLite connection. It will result in 'out of sequence' errors and SQLITE_MISUSE result codes. I don't believe that recovery from such errors is really supported.
Now I have two questions:
1) is it better using multiple connections to the same database (surely locks will be issue?)
Yes and Yes, locks will be an issue.
Locking errors can be recovered from, though.
http://sqlite.org/lockingv3.html
2) a friend of mine voiced opinion that it is a good idea to open source my wrapper (C# .NET
on top of ADO.NET), is there a need in it?
If one does not already exist, then I'm certain that the community would welcome such a contribution quite warmly and you will benefit from having lots of peer review!
b.bum
