Re: [Firebird-docs] Help

[Helen B. <he...@tp...>]

At 11:17 AM 31/01/2003 -0500, you wrote:
>On Thu, 30 Jan 2003 14:09:19 -0500, Helen Borrie wrote:
>
> > At 05:02 PM 30/01/2003 -0500, you wrote:
> >>The most significant difference I've found is transaction control where
> >>Firebird wins hands down!  For example, Postgres does not support nested
> >>transactions.  Firebird does--you just have to name them.
> >
> > No, Firebird doesn't support nested transactions.   It does supported
> > nested stored procedures.  Firebird 2 will have named savepoints.
>
>Maybe I didn't choose my words correctly.  PostgreSQL docs seems to call
>it nested when you start another transaction before committing or rolling
>back the current transactions.

Nested transactions is where a new transaction can be started from within 
another transaction, i.e. start a transaction from within a stored 
procedure (which is, itself inside a transaction, of course).

>(I believe the book where it said
>'PostgreSQL does not handle embedded transactions' was "Postgresql:
>Developer's Handbook" by Geschwinde, Schoenig, and Schonig.)  Maybe I
>should say "simultaneous"?  If I've used the term incorrectly, sorry.
>Please let me know what I should call it.

If PGSQL won't let you start another transaction before committing or 
rolling back the current transactions, then it doesn't support multiple 
concurrent transactions.  I don't think this is quite right, either.  Could 
it be that this restriction applies where the transactions in question have 
their isolation level attribute set to one which makes them 
serialized?  (Comparable to Fb's Consistency isolation...)


>Whatever it's called (and please correct me), it was the brick wall I hit
>when I first tried porting from Oracle to PostgreSQL.  In PostgreSQL,
>committing any transaction closes all transactions.

Odd.

>In the embedded SQL I
>just I just ported, there are many instances where I run one or more named
>transactions inside another--or, at least, while another one is executing.

Transactions (in both Fb and Oracle) run in isolation from one 
another.  The isolation level determines how isolated one transaction will 
be from any others.  Transaction isolation is the mechanism by which 
concurrent transactions can run each inside its own view of the database 
state, without compromising or being compromised by the effects of other 
transactions that started before it or that start after it.

Oracle has some kind of priority system for "who wins" when multiple 
transactions commit work on rows that other transactions are still working 
on.  If  I recall correctly, this depends on the style of lock granted to 
the transaction.

With Fb, the first to commit wins, as long as the current state of the row 
bookkeeping indicates it can.  (This needs a lot more explanation to 
understand:  it's pretty lengthy and OT for here.  You can pick up docs on 
the IBPhoenix and IBObjects sites that explain the multi-generational 
bookkeeping).


>I have tested cases of five and more transactions running at the same
>time with Firebird.  This was not possible with PostgreSQL because the
>first commit/rollback closed down everything.

Mmm, if you're going back there, it would be worthwhile finding out whether 
PG transactions are serialized by default, for some reason...

Helen