Rik Barker wrote:

> Hi,
>
> Thanks to everyone who replied. Giving each thread its own connection
> fixed the problem perfectly.
>
>> Yes. I'm not sure if anyone's actually documented this behaviour (i.e.
>> embedded permitting multiple connections from separate threads of a single
>> application) but it's certainly the way it works in practice, as I've been
>> finding out recently. Although one has to be slightly careful about using
>> COMMIT to "synchronize" the view of the database between the threads, it
>> can be seriously useful for each thread to have a distinct "isolated" view
>> of the database.
>
> Could you expand on the need to be careful? Are you talking about cases
> like potential issues if 2 threads have both altered the same record?
>
> I don't think that will impact my current design, as I've got threads doing
> inserts, with selects first to avoid duplicates. It would be highly
> unlikely (though not impossible admittedly) that 2 or more threads both try
> and insert the same record at the same time.

Ahh, this is exactly what I was thinking about. Consider the following
sequence:

Thread 1 Thread 2
================= =======================
Start Transaction
Start Transaction
Select keys
Insert key "1"
Select keys
Commit
Insert key "1"
Fails

At the point where thread 1 queries the keys, thread 2 hasn't committed
the newly inserted key "1", so thread 1 doesn't see it. Hence thread 1
assumes it can insert the same key, but fails when it tries to do so.
This is the sort of thing one can run into generally, i.e. not just with
embedded although when using the embedded engine you have more
possibilities for working around it (see below).

Also note that at some transaction isolation levels, even if thread 2
had committed by the time thread 1 queries the keys, thread 1 still
wouldn't see the key "1" because its transaction started before thread
2's transaction.

One (good) solution in such cases is not to rely on querying the keys,
but to use a sequence to generate the keys instead. Another (not so
good) solution is to use some form of locking to make sure the thread's
transactions don't overlap. When using the embedded engine, one could
use a critical section when a thread enters a transaction to ensure only
one transaction is active at any given time.

Not a great method, and it rather defeats the purpose of using multiple
threads, but in some circumstances it can be a useful method.

Another thing I've run into when using the embedded engine is that DDL
statements (like CREATE TABLE) don't seem to be under transactional
control (or at least, not fully?). I'm not sure if this is a feature/bug
of Firebird in general (i.e. not just the embedded engine) and/or
whether this behavior exists in Firebird 2 as I've only played with the
1.5 embedded engine so far.

That said, I'm still hugely impressed with the embedded engine. For
something a little under 2Mb it's a seriously impressive piece of kit
with all manner of interesting uses :-)


HTH,

Dave.

--
Dave Hughes

email: dave@...