Re: [INTERFACES] DBI driver and transactions

On Mon, 3 Feb 2003, Nigel J. Andrews wrote:

> I'm getting very odd behaviour and am getting quite frustrated at fighting this
> thing. So just so I know for the future does anyone know if there are problems
> with using transactions when using cached dbi handles?
>
You will have connections that are idle in transaction on the DB
server for the duration of the time that you hold a $dbh's handle that
is not AutoCommit=1 :(

> I was using my own caching of the handle(s) but I didn't have time to work out
> who, what, why and where someone was disconnecting it so switched to the DBI
> caching connect. Note, this is NOT persistent/pooled connections.
>
> However, either there is something wrong (and it's been fixed since I loaded
> the software) or I completely fail to grasp the concept of transactions as
> understood by DBI.
>
> With AutoCommit => 0 I see a BEGIN logged right before the
> first query I send...

That is how DBD::Pg starts a transaction. When you do a AutoCommit=0,
DBD::Pg sends a BEGIN. And when you do a $dbh->commit()/$dbh->rollback(),
DBD::Pg will do a COMMIT and then a BEGIN (there has been some discussion
on this commit() behavior on dbi-dev)

>
> Oh I give up. Trying to clarify what happens when I'm now seeing rollbacks
> issued in AutoCommit => mode where I'm not doing any and it's certainly not me
> since I've got a trap on my interface layer to DBI for that and it's not been
> tripped at all.
>
IIRC, It will do a rollback for you on disconnect.

> So,
>
> a) anyone know of any problems,
>
> b) can I assume it is me and not just give up on this DBI stuff in the future (
> I haven't got the time to rewrite my application now; not that I can't see it
> being a particularly onerous task for what I use from DBI )
>
> c) can I just issue BEGIN, COMMIT, ROLLBACK when I want to via normal
> $dbh->do(...) without causing myself even more problems?
>

Yes and no. DBD::Pg will stop you from calling those commands;
however, I think that you can trick DBD::Pg by make it not look like a
transaction command (eg. $dbh->do(q{/* Trick Pg */ BEGIN}) ); although, I
have not tried it, and it is not recommended.

I, for the most part, do:

sub stuff {
local($dbi->{AutoCommit}) = 0;

eval {
#do transaction stuff.
$dbi->commit();
}; if (my $e = $@)
$dbi->rollback();
}
# $on exit $dbi->{AutoCommit} will go to 1 and DBD::Pg will call
# commit() for you
}

DBI now supports a begin_work method, but DBD::Pg does not have
support for it at this time.

-r