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

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

"Mark D. Anderson" wrote:
> 
> > > When AutoCommit is on, it appears its current behavior is incorrect.
> > > IMHO it should:
> > > 1. Not maintain an always active transaction.

Now a transaction is active only when needed, and committed as soon as
the operation is done, when AutoCommit is On.

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

Yes, the do() method (in InterBase.xs) has its own way instead of let
the DBI take the conservative way (dbd_st_prepare - dbd_st_execute).

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

when AutoCommit is Off, for now I still use isc_commit_retaining().
Could you provide test cases to show that isc_commit_retaining() will
cause locking problems?

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

Thanks. The recent modification is based on DBD::Pg's route to handle
the AutoCommit attr.
Take a look at the latest code (0.20.2), alternatively you can do cvs
checkout:
cvs -z3
-d:pserver:ano...@cv...:/cvsroot/dbi-interbase
co dbi-interbase

At this point, I have some notes that I'd like to discuss:
1. other drivers (eg Oracle) use SET TRANSACTION sql command to specify
isolation level. 
Because the hierarchy is: database handle -> transaction handle ->
statement handle, it seems that changing isolation level affects the
whole statement handle (within the same DBI dbh).
2. I have a specific problem when running Jochen's test scripts, for
example t/50chopblanks.t. 
After the second $sth->finish, it does a DROP TABLE: $dbh->do("DROP
TABLE ...");
It is fine with mysql, but not with InterBase. 
Within dbd_st_finish(), it only calls isc_dsql_free_statement() with
DSQL_close as the last arg,
ie, it only _close_ the cursor, not drop the statement.
Statement is actually drop within dbd_st_destroy, this is what I read
from DBI::DBD.
Is it a normal behavior (so we can say that the test script should be
fixed), or there's something wrong with my implementation? Have you seen
the same behavior with other drivers?

Rgds,
Edwin.