At 10:57 PM 3/11/2004 -0500, you wrote:

>:: Assuming you are displaying this is a grid or something
>:: similarly Delphish, put the SELECT statement into a
>:: read-only, ReadCommitted transaction
>
>If I have a bunch of select statements - if I don't care about consistency
>is it faster to put them all in a read transaction or just run them
>separately?

Faster? you have to "run" each statement, whether it's in a transaction on
its own or with other statements.

>I assume if they are all in one the would create more read
>locks..

There are no "read locks". The choice of them being in separate
transactions or all in one is a question of whether you want them to be
consistent with one another, i.e. all part of a single task and therefore
needing to deliver a consistent view.

>So is it better just to let them run each in a separate transaction?

See above.

>:: Put this statement in the thinnest possible update object
>:: that runs in an explicitly controlled transaction in
>:: configured with concurrency isolation and no wait lock
>:: resolution (not autocommit!!). Read on...
>
>Both of the above would bein one transaction. I need it long enough just to
>get the PK and mark it as used.

Sorry, that doesn't make any sense. If the SELECT is in a read-only
transaction, then the update statement has to be in a different transaction
- a read-write one. You can't perform updates in a read-only
transaction: an update is a write.

>What is autocommit?

It's where the transaction in which a statement runs is committed
automatically. You can't roll it back.

There are two "autocommits". One is "Server Autocommit" (set by the TPB
attribute isc_tpb_autocommit) which can be used only for executable
statements (not selects). It's typically used when executing data
definition statements in scripts. The server takes care of both starting
and committing such a transaction and, obviously, binds a statement and a
transaction in a one-to-one arrangement.

Then there is the client-side AutoCommit that was invented by Borland to
support its implementation of the Delphi data access model. This is the
one that causes the problems with the TSB. Cosmetically, it has the same
effect as ServerAutoCommit, i.e. executes and commits a statement in one hit.

However, it uses a "soft commit", a.k.a COMMIT WITH RETAIN, or
CommitRetaining. It has the extra "bonus" (necessary for Delphi) of being
available for selects. However, although this "soft commit" does commit
the work (makes the changes visible to other transactions) it holds the
database cursors of your SELECT statement open and puts a block on
post-commit cleanup. So - instead of the TSB being maintained in a smooth,
roughly FIFO manner, new transactions just keep getting crammed into the
TSB and never getting cleared out. These long-running read-write
transactions, that are continually committed with CommitRetaining, cause
cleanup to get "stuck" at these much lower (= older) transaction IDs.

>:: Pessimistic locking is available in Fb 1.5 through the the
>:: SELECT...FOR UPDATE WITH LOCK syntax. It needs to be used
>:: with great discretion on a very small set (preferably one
>:: row) with a very short life and with a total understanding
>
>This would be for one row - but I cannot rely on FB specific syntax.

I urge you ask on the net-provider list what you are actually getting in
your defaults, how you can vary the defaults and to what degree you can
take advantage of the server's capabilities. Generic interfaces always
take things down to the lowest common denominator. It's quite useless
looking for a solutiion based on transaction configurations that the driver
doesn't support. Both the Java and ODBC drivers suffer from this inherent
"dumbing down", so it would be pretty amazing if the .NET driver were
different.

./hb