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

[Edwin P. <ed....@co...>]

"Mark D. Anderson" wrote:

> 
> InterBase.pm - Today
> --------------------
> InterBase.pm takes the approach of always having exactly one current open
> transaction for each connection, stored in ib_trans_handle, regardless
> of AutoCommit. It has a _commit() function which does an underlying commit,
> then creates a new transaction for the connection, so that it always has one.
> 
> If AutoCommit is off, it exposes commit() and rollback() functions.
> It begins a transaction at connect, and does a rollback at disconnect.
> 
> If AutoCommit is on, it disables explicit commit() or rollback().
> It automatically commits the previous transaction and starts a new
> one after each do(). So far so good; but for execute(), when AutoCommit:
> 
> 1. It does a _commit *before* the operation. This should just have the
> effect of throwing away an unused transaction, because in autocommit mode
> there should never be pending operations in an open transaction. Except
> that isn't true because of 3 and 4....
> 
> 2. Then it does the underlying execute.
> 
> 3. Then it does another _commit(), but only if the operation is a non-select.
> 
> 4. When later a fetch() is complete or finish() is called, it doesn't do a _commit.
> 
> This has the effect that for select operations, the client gets a very long-running
> transaction, because it is never committed when it is done. This situation is
> made worse because of IBPerl behavior which defaults all transactions to be mode write.
> 

You have raised important points regarding transaction implementation.

> The Fixes
> ---------
> IBPerl should implement transaction parameters. I have sent some starter code
> directly to Bill Karwin.
> 
> InterBase.pm needs some restructuring.
> 
> When AutoCommit is off, it appears its current behavior is basically correct, though I
> know if I was using it I would want direct access to transaction creation
> (if I care enough about transactions to turn off AutoCommit, then I care
> enough to stipulate their parameters). See end of this section for a suggestion.
> Also it appears to me from visual inspection only that rollback() is not creating
> a new transaction, and it should if it is to be consistent with the rest of the code.
> 
> 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. 
2. Performance degrades, because a new transaction is created for each
new statement handle. 

> 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().

> 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.

> 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?

> That transaction is automatically committed if AutoCommit, and explicitly if not.
> Also, commit() and rollback() should take an attribs argument as well.

Rgds,
Edwin.