Helen Borrie wrote:

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

No, sorry if I gave the impression that I was commenting on the AVs
issue. I was responding to Rik's query about cases where one needs to be
careful with regard to multiple threads accessing an embedded database
(implying multiple connections and hence multiple simultaneous
transactions), specifically the point where he mentioned he was doing
inserts "with selects first to avoid duplicates" (hence my later
comments regarding sequences as a means of avoiding such problems).

[snip]

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

Absolutely; a connection should "belong" to one and only one thread.

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

Yup, wasn't suggesting it wasn't, just that it's something to be aware
of when dealing with multiple simultaneous transactions.

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

Indeed.

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

Yes, I should have labeled that one more clearly: "not so good" -> "very
very bad".

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

Will do (assuming I can re-create it, as you note above I may be
entirely wrong on 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.

No, definitely not trying to use the embedded engine in the development
environment (I rapidly discovered that was a bad idea).

However, instead of installing the full server, I've found no trouble
just doing simple "runtime debugging", which is to say I've forgone the
pleasures of fancy modern breakpoints and stepping, and have been
getting along quite happily with sticking the odd debugging "print"
statement here and there, running the application (with the embedded
engine), and checking the output.

(okay, so I'm being a debugging luddite, using the fancy IDE as little
more than a glorified editor ... after recently spending some time doing
Python with little more than an editor and a shell, I haven't quite
gotten out of "old school" mode yet :-)


--
Dave Hughes

email: dave@...