<snip>
I wrote

>> Not at all. I believe the idea is that after COMMIT AND CHAIN, you
>> see
>> all updates of other transactions committed before your commit. I
>> think the purpose is to avoid requiring an immediate BEGIN TRANSACTION
>> call. However, I don't know exactly what they mean by access mode or
>> diagnostics area limit
>

Lester Caine wrote

> Since the SQL2003 is a typical vague standard, it's the similarities
> and
> differences I'm trying to master.
> So AND CHAIN would be
> COMMIT X
> SET TRANSACTION X ......
> where ...... matches the original transaction
> Is the bit I am missing here that RETAIN keeps it's CURSOR settings as
> they were before the COMMIT? Since I'm normally accessing Firebird via
> IBO or PHP, some of the subtleties get hidden :)
>

I'm getting into areas I'm not 100% certain of here, but, sticking my
neck out, I think:
COMMIT AND CHAIN

is the same as
COMMIT
(see all effects of other committed transactions)
BEGIN TRANSACTION
(no relationship between first and second transaction)

I think sql has no concept of a snapshot. My understanding of
firebird's COMMIT RETAINING is that it applies permanently to durable
storage all changes made to the snapshot as of the transaction start,
and starts a new transaction based on this modified snapshot. No
changes made by other transactions are visible to the "retained"
transaction. As a side effect, cursor positions should be unaffected.

In other databases, or running at read committed "isolation", there are
no snapshots, so +- retaining should have no effect.

david jencks