Re: [Dbi-interbase-devel] DBD and Interbase functionality

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

> > In summary
> > Interbase keeps track of transaction context in the database handle.

actually InterBase is just about the only RDBMS I know of that does not 
do this. It is one of the few whose C API allows for explicit manipulation
of multiple, independent, concurrent transaction contexts on a single database
connection.

But DBI after all these years still has no standardized transaction API.
Plus, no one ever writes DBD implementations from scratch :).
So DBD::InterBase ended up following the common pattern.

Even though the InterBase C api requires a transaction handle for both
prepare and execute, a prepared statement handle should certainly
be able to survive a commit (i.e. prepare and execute in different transactions),
or there wouldn't be much point to a prepare.

So there should be some way to make the perl code work unchanged.
But the isc_* C api is so messy i long ago gave up on understanding its subtleties....

> > I believe that my rejigged test code (not tested) will solve this problem in
> > this simple case, however for other situations (like nested select
> > statements) the best solution is to manage your own transactions or
> > investigate the new ib_softcommit driver specific option.

explicit commit should take care of it,
if you don't mind the extra code.

ib_softcommit changes semantics so it might not be a solution.
isc_commit_retaining widens transaction windows, which can result
in starvation in multi-user scenarios.
(i know this, because the first releases of DBD::InterBase *always*
used isc_commit_retaining, with no recourse from the perl side.)

> The modified script should work, but only because it avoids the problem
> -- which seems in this case the best solution :-)
> 
> AFAIK this only happens in one place, so I may see about modifying that
> code.

Using DBI's builtin prepare_cached() support might be easier, if you are open
to changing your code.
That way you needn't do explicit prepare calls at all.
Since the prepare cache for a particular statement won't be set until the first
time it is actually executed, you get the same call order as in your changed
code. See the source to DBI.pm for more info.

-mda