At 07:20 AM 20/05/2006, you wrote:

>Rik Barker wrote:
> > Hi,
> >
> > Thanks to everyone who replied. Giving each thread its own connection
> > fixed the problem perfectly.
>
>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).

Well, it's true of course. Embedded is absolutely no different to a
full server with respect to transaction isolation. But you're on the
wrong track if you think that update conflicts cause the AVs when you
attempt to multi-thread a connection.

The cause of the AVs is in the memory structures that the client uses
to manage the "handles" by which it references stuff and manages the
contexts of its connections. The client's "base session", which is a
thread, consists of one connection to each of one or more databases,
a moving cloth of transactions involving those connections and
another moving cloth of execution requests within the (separate)
contexts of those transactions. So, execution of a request occurs
within a "session" consisting of one connection (or more, if
multi-database transactions are potentially involved) and one transaction.

The key point here is that the client has to *know* which
connection(s) a transaction and its associated requests belong
to. Inside its own thread ("base session"), no problem.

But if your application spawns another thread containing execution
requests that refer to the handles in the base thread, you have
created the conditions for memory protection faults. It is your
*application* that is doing this damage, since the API layer - the
client library - isn't threadable. If the application creates a
thread that doesn't have a complete context (connection[s],
transaction[s] belonging to that [those] connections and requests
within that [those] transactions, then you have made trouble.

So the rule must be: if you spawn threads that do data access, make
each thread an isolated session between the client and the
database[s]. Create a session (connection[s], transaction[s] and
sets) purely within the thread and destroy them before the thread terminates.

>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.

As designed.

>One (good) solution in such cases is not to rely on querying the keys,
>but to use a sequence to generate the keys instead.

This applies in any case, it's not confined to embedded or
superserver or whatever. The system is designed to prevent duplicate
keys. It's up to the database designer to provide safe ways to
ensure that keys are not prone to duplication. Generators
(sequences) are provided for this purpose. Any model of key
generation that relies on the existing values in the table is poison
for a transactional database, threading or no threading.

>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.

No. There is no need to cripple the features of the database engine
as a workaround for bad programming and bad database design. The
solution is to fix the programs and the design.

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

The circumstances where pessimistic locking is useful are very
rare. And pessimistic locking doesn't solve the memory fault
conditions created by trying to reference the context of one thread
from another thread.

>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?).

Quite untrue. The only thing in Firebird that is not subject to
transactional control is the generation of sequences from generators.

>I'm not sure if this is a feature/bug of Firebird in general (i.e.
>not just the embedded engine)

It's doesn't exist, as either a feature or a bug. Create a new
thread (list thread) with an appropriate Subject and try to describe
what you have experienced that leads you to believe this.

>and/or whether this behavior exists in Firebird 2 as I've only
>played with the
>1.5 embedded engine so far.

It doesn't exist in v.1.5 or v.2.0. If you have only "played with
the embedded engine so far", one has to suspect that you're bumping
into problems trying to use the embedded engine as a back-end to
development. The embedded engine is not intended for this. It is a
deployment model.

>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 :-)

But not useful or recommended in a development environment....

./heLen