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