Re: About prepared statements asnd transactions....

On Wed, 2005-10-12 at 12:58 -0400, Edward J. Sabol wrote:
> Ed wrote:
> >> DBD::Mysql and DBD::Pg (for older versions of PostgreSQL only) emulate the
> >> capability of having multiple active sth's per dbh by reading all the data
> >> from an unfinished sth into memory when a second sth is executed. I
> >> suggested adding similar emulation of this capability to DBD::Sybase to
> >> Michael in February 2004, but nothing came of it, AFAIK.
> 
> Michael replied:
> > It's feasible, but it is potentially problematic if the original
> > $sth has a lot of pending data. I guess it could be an option.
> 
> Agreed, but transparently opening a second connection to the database and
> violating transactional integrity seems even more problematic to me.

Which is why opening a second statement handle while autocommit is off
is a fatal error.

The relevant C code is in dbdimp.c around line 2726 (in the current
version). This says that if a new statement is being prepared while an
existing statement is "active", and if autocommit is turned off, then we
throw a fatal error.

Personally I'd agree with Ed Avis - managing the transactional state of
multiple active statement handles is tricky, and I don't have enough
time (or energy) to make sure that I trace all the possible conditions
and handle this correctly. So I will leave that code as it is.

What I'm interested in, however, is the situation where you've managed
to get transactional inconsistency when using AutoCommit off mode and
having multiple prepared statements. Here I should add that prepared
statements that have placeholders (i.e. that generate a temporary stored
procedure) can be re-executed with no problem, and are NOT considered
active after all the results from the execution have been fetched.

This means that you can legally have more than one such statement in the
prepared state with AutoCommit off, but you can only execute one of them
at a time.

This, BTW, is what is supposed to be tested in the t/multi_sth.t test
script. However, re-reading the script I see that I don't really test
the AutoCommit off situation, and of course this only tests SELECT
queries.

If someone in the audience has the energy to write a simple test case
with multiple updates (with and without autocommit) then I'd happily add
it to my test suite, and if applicable to the distributed test suite as
well.

Michael
-- 
Michael Peppler  -  [email protected]  -  http://www.peppler.org/
Sybase DBA/Developer - TeamSybase: http://www.teamsybase.com/
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html