[Interest] Sqlite "multithreaded" application

[Till Oliver Knoll]

2011/10/26 Michele Ciampichetti <cia.michele at gmail.com>:
> GoodMorning to everybody.
> I've a problem with QSqlite driver: I create an application that use an
> SQLite DB by mean of QSqlite Driver. Now. I'd like to makfore it a Network
> application, in which some user access the same DB from different instances
> of this application on different machines.
> I tryed it but, the concurrent access to QSqlite create some mistake.

Now your email is a bit confusing as you are throwing multiple issues
into a single question, such as "multi-threaded" vs "multi-user", and
also the issue about how to deploy your Qt plugins.

It is also not entirely clear what you mean by "create some mistake":
is the data in the DB not modified or read as expected? Do you manage
to connect to the DB at all?

I am leaving away any discussion about how to deploy the Qt plugins.
That's properly described in the Qt docs. If you have any questions
about deployment I suggest you post a separate question about that.

For now I assume you managed to connect to the QSqlite DB instance,
you have multiple clients connecting to a single "server", and this
"server" is solely accessing the QSqlite DB via multiple threads (I
guess you mean one thread per client).

First off, I have zero experience using Qt to connect to a concrete DB
instance, so I simply assume you manage to establish a connection "per
client". I also must admit that I don't have experience with SQLite,
apart that I know it's a "file-based, feature-stripped down DB". So
your real issue is that the data in your DB is corrupted or "not
expected".

Given that, the first thing you have to realise that almost EACH DB
(Oracle, DB2, MS SQL Server, MySQL, SQLite, ...) is handling
concurrency DIFFERENTLY! As a matter of fact that is NOT part of e.g.
SQL99 specification.

So a simple

  SELECT foo
  FROM bar
  WHERE id = 42;

might already give different results in a multi-user accessed DB!

For instance Oracle NEVER blocks READs (it returns a "consistent
result" instead, which might not be the latest result), whereas DB2
might very well block above SELECT statement, until the lock on the
given row is freed. Not to mention there are an estimated gazillion of
(DB specific!) ways how you can control DB locking on a
transaction/row level!

So what you want to read up is probably the following:
  http://www.sqlite.org/faq.html#q5

"Multiple processes can have the same database open at the same time.
Multiple processes can be doing a SELECT at the same time. But only
one process can be making changes to the database at any moment in
time, however."

However this statement is already followed by lots of caveats such as
"does not work on NFS mounted drives", "on FAT only when Share.exe is
running" and "on Windows 9x only when the moon is shining".

So get familiar with the locking mechanisms and capabilities of SQLite first.

If that doesn't work out for your needs then you can always
synchronise in your "server" application ("naive and brute force: make
sure only one thread at a single time has access to the DB"), with the
usual Concurrent Programming patterns. Qt provides quite a bunch of
functionality here! But that comes along with additional complexity in
your code and with a performance decrease (but since you are using
SQLite I guess performance is not your issue anyway ;))


As to answer your basic question: yes, you can have multiple
connections to your SQLite, but you want to be careful about how to
synchronise your DB access (*)! It's not free! Get intimidate with the
concrete DB you are using (and the same SQL code most likely won't
work on a different vendor DB: there's ALWAYS a case which behaves
differently under multi-user conditios on another DB (and Murphy's Law
states that a) this case only happens "in production" and b) on the
very first day your users are hitting the DB ;))


Good luck!
  Oliver

(*) If on the other hand you are already failing at the step how to
connect to your DB multiple times using Qt please clarify your
question.