Helen,

I previously wrote INSERT because it really is a two step INSERT: SELECT
and APPEND, then UPDATE the BLOB. The two threads never update the same
record.

The reasons for multi-threading the application have to do with
throughput and responsiveness of the user interface. However, I think
you uncovered something that was not known to me, later in this thread.
I never knew that the Firebird client wasn't thread-safe and just
assumed it was (my fault). I have added thread synchronization to all
of the database interaction and the errors have disappeared, so, you
were right, the threads are not completely isolated after all.

Thanks for taking the time to respond.

Dan Cumpian


-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Monday, February 24, 2003 11:58 PM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] Help with Multi-threaded application on
Localhost

At 10:37 PM 24/02/2003 -0500, you wrote:

>Helen,
>
>The "request live" is in a select query. The select query is used to
>open the record where the blob needs to go. I am using a TBlobstream to
>update the blob itself.

Previously, you said you were inserting. Now you say you are
updating. Yes, of course you will have have transaction concurrency
issues
if one transaction in one thread tries to update a row that is requested

live in another transaction in another thread.

>As far as environment goes, I'm not certain how to answer this. I am
>using the Zeos Database Object and Delphi to connect to the Firebird
>database.

That's close enough. One supposed that Request Live was a client layer
function but it's not very revealing if one doesn't know what your
client
layer is.

>Each thread has its own, independent database connection. I'm
>not sure what you mean regarding your last question... Basically, the
>application is a Newsreader that has the following threads:
>
>1) GUI thread
>2) Download thread #1
>3) Download thread #2
>4) Purge thread
>5) Cleanup thread
>
>The last two threads are not usually active when the error occurs. The
>GUI thread displays the download queue in a DBGrid as well as handling
>other database related duties required by the user (displaying headers,
>etc...). The download threads do the actual work.

So where are your updates occurring?

>In your opinion, are these separate database connections independent
>enough to provide the isolation required? Is so, then I will continue
>to try and track this error down.

I don't know whether the ZEOS components have a "session" layer above a
connection. IBO, for example, provides for absolute isolation by
cocooning
the entire data access structure inside a distinct session object.
Without
threading, the application creates a default session as a shell around
everything. For threads, it has an explicit session component to use
instead of the default session. You provide one such "shell" for each
connection *context* (which can be connections to multiple databases,
but
not multiple connections to the same database).

But I doubt that connection isolation is the main problem here, although
it
may be the source of the lost cursor problem.

>FYI, I am using these same components to connect to a MySQL database in
>this same application and I don't get this error there (though there

are

>other issues with MySQL).

MySQL doesn't have transaction isolation so I guess you are getting
filesystem i/o problems with it. What's relevant to transaction
isolation
in Fb is irrelevant to MySQL.

>How can I force each thread to wait, when necessary, for a table to be
>available when it is being updated in another thread?

What you have is a single user who is looking at a single set of data in

five different transactions, each thread performing a task which, in
some
fashion, not described so far, is dependent upon the completion of
others. ReadCommitted lets one transaction "see" the effects of work
committed by another, but it doesn't provide any control over which
transaction commits first. If LockWait is true, then you are liable to
get
a "livelock", where each transaction just waits forever for the other to

commit, never raising a locking exception. If it is false, you'll get a

locking conflict exception of one kind or another.

You could use Concurrency (snapshot) transaction isolation with LockWait

false, as long you had some means to control which transaction gives way

to which...handling the transaction where the lock conflict occurs by
means of a retry timer. I don't see a simple answer to this when you
have
five threads in the hands of a single user, with each thread doing its
own
thing in its own time-scale.

I'm probably just being thick, but I can't see the objective of
threading
this appl. This scenario appears to want all of the activities to be in
a
single transaction - that means one thread, one connection. Is there
some
special and overriding reason why this has to be threaded?

heLen




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/