[Dbi-interbase-devel] Re: fixing InterBase.pm and IBPerl.pm for transactions

[Mark D. A. <md...@di...>]

> > When AutoCommit is on, it appears its current behavior is incorrect.
> > IMHO it should:
> > 1. Not maintain an always active transaction.
> 
> I'm afraid that we can't do this:
> 1. ANSI/ISO standard for SQL states that a database connection is always
> _in_ active transaction. 

i realize you have to have a transaction when you send operations.
i'm suggesting that for cleaner code, i don't see the point in maintaining a
state variable ('ib_trans_handle') when in autocommit mode, since in that mode you can
just begin and end a transaction around each individual operation.
Doing away with the variable (at least for autocommit mode) would also
set up for cleaner implementation of the proposed 'Transaction' attrib.

> 2. Performance degrades, because a new transaction is created for each
> new statement handle. 

Well, that is up to the user to decide -- what do you think autocommit means,
after all?

the only circumstance where i could imagine doing it is if you had a special
transaction to be used for readonly operations, that had its isolation parameters
set down low, so that it could be re-used again and again without creating a
huge conflict window.

that would then correspond to the situation that some rdbms's offer but ib
does not, of performing selects "outside" of any transaction context.
the effect would be the same -- the selects would be in a transaction, but
one in which its parameters have trimmed away all the isolation.

> > 2. Not use the _commit() function at all, as it stands.
> > 3. For do(), it should create a new transaction right before, and commit it right after.
> 
> Now we use isc_commit_retaining(), which doesn't close an active
> transaction. this should give better performance for we don't have to
> start a new transaction after a commit. We use isc_commit_transaction()
> only in dbd_db_disconnect().

this is likely to create lock timeouts in other connections, depending on what transaction
parameters you have. for greater throughput, you want to have the smallest transaction
profile possible. InterBase may have some pseudo-magical multi-versioning capabilities,
but that doesn't alter the realities of ACID semantics.

> 
> > Using the default IBPerl transaction should be fine.
> > 4. For execute() of a non-select, it should do the same thing as do().
> > 5. For execute() of a select (or select-like operation), it should create a new read-only
> > transaction right before the underlying execute. It should then commit that transaction
> > in fetch() and finish() (being careful to do the right thing if fetch calls finish).
> 
> execute() must be run on an active transaction.

that is what happens in my above pseudo-code.

i might note that the above is not just my intellectual analysis, this is based on looking at
how other rdbms dbd drivers work.

> 
> > As an enhancement request, I would suggest that prepare() and do() take an optional
> > Transaction key in the $attribs, which is a IBPerl::Transaction.
> > If that exists, it is used instead of one automatically created.
> > This is true whether AutoCommit is set or not.
> 
> This looks like a great idea! This way users can gain greater control on
> the transaction isolation level.
> But this means that we must keep another transaction handle within
> imp_sth, to let the _default_ transaction handle stored within imp_dbh
> untouched. But this structure looks strange. Any idea?

the strange part to me is the default_transaction_handle, at least for autocommit.
it seems perfectly expected that a statement should have its own transaction handle.
after all, that is how the underlying api works from the rdbms vendor.

-mda