Thread: DBI driver and transactions

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?

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

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.

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?

and
d) I've said it before but what's the point of DBI if it's not to make life
easier?


This on a linux debian potato with:

Pg      7.2.3
Perl    5.8.0
DBI     1.30
DBD::Pg 1.13


--
Nigel J. Andrews

Do you want autocommit on or off?

With autocommit off, use $dbh->rollback to do ROLLBACK followed by BEGIN
and use $dbh->commit to do COMMIT followed by BEGIN.

Don't use the $dbh->do stuff for that sort of thing.

It could be you, or could be DBI, depends on what you really want to do
(can't tell from your message). If you are trying to catch asynchronous
notices with autocommit off, DBI might not be a good choice.

But if you're trying to do run of the mill stuff, it's probably you :).

Link.

At 02:18 AM 2/3/03 +0000, 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?
>
>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...
>
>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.
>
>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?

> Date: Mon, 3 Feb 2003 02:18:11 +0000 (GMT)
> From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
>
> With AutoCommit => 0 I see a BEGIN logged right before the
> first query I send...
>
This is in compliance to the SQL2 standard, which says that
transactions implicitly begin with the first DML command after
the last commit/rollback. As Postgres has explicit transaction
beginning, the DBD-Pg driver must emulate the SQL2 behaviour by
automatically issuing a BEGIN statement.

DBI provides the methods begin(), commit() and rollback() for
transaction management; begin() usually does nothing and thus
does no harm when you call it.

Christoph Dalitz

On Mon, 3 Feb 2003, Christoph Dalitz wrote:

> > Date: Mon, 3 Feb 2003 02:18:11 +0000 (GMT)
> > From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
> >
> > With AutoCommit => 0 I see a BEGIN logged right before the
> > first query I send...
> >
> This is in compliance to the SQL2 standard, which says that
> transactions implicitly begin with the first DML command after
> the last commit/rollback. As Postgres has explicit transaction
> beginning, the DBD-Pg driver must emulate the SQL2 behaviour by
> automatically issuing a BEGIN statement.
>
> DBI provides the methods begin(), commit() and rollback() for
> transaction management; begin() usually does nothing and thus
> does no harm when you call it.

Thanks for the replies folks.

Indeed I understand that. I wasn't surprised to see the begin withheld until
the first real query. What I was completely baffled by and probably didn't
explain clearly in the next paragraph was why there was a rollback issued later
but before I had even considered issuing commit/rollback in my code.

So just to clarify. To use transactions with DBI:

$dbh = DBI->connect_cached("blah",  "more blah", "secret",
                           { AutoCommit => 0, Raise Error => 1 });
...
$dbh->prepare("SELECT...");
...
$dbh->prepare("INSERT...");
...
$dbh->prepare("INSERT...");

$dbh->commit;

...more stuff

?

So any ideas what this message on that commit is?

disconnect(DBI::db=HASH(0x87af40c)) invalidates 1 active statement. Either destroy statement handles or call finish on
thembefore disconnecting. at /home/nandrews/src/www/lib/LtS/perl/DB.pm line 128. 
        DB::disconnect('Delegate=HASH(0x87afeb0)') called at /my/directory/modulesA/DB.pm line 144
        DB::DESTROY('Delegate=HASH(0x87afeb0)') called at /my/directory/modules/BookingSummaryPage.pm line 142
        eval {...} called at /my/directory/modules/BookingSummaryPage.pm line 142

Sorry for the formating, I didn't want to add line breaks where the original
message doesn't have them. The way this just refuses to work for me I wouldn't
be surprised if such things were critically important.

DB is a wrapper class I have which uses DBI and DB::disconnect calls
DBI::disconnect. Delegate is one of my classes and happens to be the last one
which issued an insert or update down the connection. However, the commit call
is made after that particular operation has completed, i.e. the commit occurs
after calling a method of a different class which does an insert. Delegate
inherits from DB, via another intermdiary class.

I fail to see where I'm going wrong and what that error message is really
saying. I'm going to switch to using normal connects to see if that makes any
difference, although I'm pretty sure I was getting similar messages at an
earlier stage of development before I switched away from my own connection
caching to use DBI's.

BTW, is there a better list for discussing such DBI questions?


--
Nigel J. Andrews

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

> On Mon, 3 Feb 2003, Christoph Dalitz wrote:
>
> > > Date: Mon, 3 Feb 2003 02:18:11 +0000 (GMT)
> > > From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
> > >
> > > With AutoCommit => 0 I see a BEGIN logged right before the
> > > first query I send...
> > >
> > This is in compliance to the SQL2 standard, which says that
> > transactions implicitly begin with the first DML command after
> > the last commit/rollback. As Postgres has explicit transaction
> > beginning, the DBD-Pg driver must emulate the SQL2 behaviour by
> > automatically issuing a BEGIN statement.
> >
> > DBI provides the methods begin(), commit() and rollback() for
> > transaction management; begin() usually does nothing and thus
> > does no harm when you call it.
>
> Thanks for the replies folks.
>
> [lots of my blurb edited out]
>
> So any ideas what this message on that commit is?
>
> disconnect(DBI::db=HASH(0x87af40c)) invalidates 1 active statement. Either destroy statement handles or call finish
onthem before disconnecting. at /home/nandrews/src/www/lib/LtS/perl/DB.pm line 128. 
>         DB::disconnect('Delegate=HASH(0x87afeb0)') called at /my/directory/modulesA/DB.pm line 144
>         DB::DESTROY('Delegate=HASH(0x87afeb0)') called at /my/directory/modules/BookingSummaryPage.pm line 142
>         eval {...} called at /my/directory/modules/BookingSummaryPage.pm line 142
>
> [lots of my blurb edited out]
>
> I fail to see where I'm going wrong and what that error message is really
> saying. I'm going to switch to using normal connects to see if that makes any
> difference, although I'm pretty sure I was getting similar messages at an
> earlier stage of development before I switched away from my own connection
> caching to use DBI's.
>
> BTW, is there a better list for discussing such DBI questions?
>

One that did also occur to me is that I don't call finish before methods which
have done prepare/execute and that many times I don't use a loop to retrieve
data until one of the fetchrow_...() methods says there isn't any left. Could
this be the cause of my problems? Somehow messing with DBI's mind and making it
run out of statment handles? It does say finish or destroy handle and the
handles should be destroyed when they drop out of scope shouldn't they?


--
Nigel J. Andrews

At 09:31 AM 2/3/03 +0000, Nigel J. Andrews wrote:

>One that did also occur to me is that I don't call finish before methods which
>have done prepare/execute and that many times I don't use a loop to retrieve
>data until one of the fetchrow_...() methods says there isn't any left. Could
>this be the cause of my problems? Somehow messing with DBI's mind and
>making it
>run out of statment handles? It does say finish or destroy handle and the
>handles should be destroyed when they drop out of scope shouldn't they?

Yep before you disconnect/quit, you're supposed to finish active
statements. e.g. prepare, execute, use up results, or call finish if you
don't need the rest of the results.

DBI by default buffers results, so if you don't call finish the buffered
results hang around, and if you just quit abruptly without tidying stuff
up, it grumbles (but still should clean up).

HTH,
Link.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Yep before you disconnect/quit, you're supposed to finish active
> statements. e.g. prepare, execute, use up results, or call finish if you
> don't need the rest of the results.

Exactly. The error only appears after you have done a prepare *and* a
select, with no concomitant finish or fetching. Here is a code sample:

use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
         {AutoCommit=>0, RaiseError=>1, PrintError=>0});

my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");

my $count = $sth->execute(120);

## Exiting here will cause the warning described

if ($count eq "0E0") {
  $sth->finish();
}
else {
  my $info = $sth->fetchall_arrayref({});
  ## Do something with info...
}

## Exiting is now safe: commit and disconnect are separate issues...


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302030912

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+PnomvJuQZxSWSsgRAhTMAKDLEzXWlGdEQJWKZ72qafkkBL9PRQCfVuh3
uFGqo2u41jwQFONCT9VBAks=
=axjF
-----END PGP SIGNATURE-----

On Mon, 3 Feb 2003 greg@turnstep.com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Yep before you disconnect/quit, you're supposed to finish active
> > statements. e.g. prepare, execute, use up results, or call finish if you
> > don't need the rest of the results.
>
> Exactly. The error only appears after you have done a prepare *and* a
> select, with no concomitant finish or fetching. Here is a code sample:
>
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
>          {AutoCommit=>0, RaiseError=>1, PrintError=>0});
>
> my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");
>
> my $count = $sth->execute(120);
>
> ## Exiting here will cause the warning described
>
> if ($count eq "0E0") {
>   $sth->finish();
> }
> else {
>   my $info = $sth->fetchall_arrayref({});
>   ## Do something with info...
> }
>
> ## Exiting is now safe: commit and disconnect are separate issues...


Thanks for that clarification. I hadn't been worrying about it in my code.
With the database design I either expect 1 or 0 rows from queries in which I
treat which ever is appropiate as a problem or I am expecting 0 <= n in which
case I do fetch all the results.

I presume that the issue here is that in the 1 or 0 rows returned case where 1
row indicates I have a problem then I am not fetching from or finishing that
statement. Although, in the debugger I've seen the Active flag still set after
doing a single fetch on a 1 row resultset.

Now I'm just going to added finishes everywhere. Nasty, time consuming and
probably excesive but also probably less time consuming that trying to be
selective and missing one that's necessary.


--
Nigel J. Andrews

Ok, bug found, and I didn't even have to go through all my code inserting
finish() calls. Reusing Greg's example code, the problem was in a method
(actually a constructor) like:


> On Mon, 3 Feb 2003 greg@turnstep.com wrote:
> >
> > > Yep before you disconnect/quit, you're supposed to finish active
> > > statements. e.g. prepare, execute, use up results, or call finish if you
> > > don't need the rest of the results.
> >
> > Exactly. The error only appears after you have done a prepare *and* a
> > select, with no concomitant finish or fetching. Here is a code sample:
> >
package blah;

use DBI;

sub new {

   my $self = {};

   my $dbh = DBI->connect("dbi:Pg:dbname=foobar", $user, $pass,
                          {AutoCommit=>0, RaiseError=>1, PrintError=>0});

   $self->{dbh} = $dbh;

   my $sth = $dbh->prepare("SELECT * FROM baz WHERE waldo > ?");

   my $count = $sth->execute(120);

   ## Exiting here will cause the warning described
   return undef unless $count;

   if ($count eq "0E0") {
      $sth->finish();
   }
   else {
      my $info = $sth->fetchall_arrayref({});
      ## Do something with info...
   }

   ## Exiting is now safe: commit and disconnect are separate issues...

   return bless($self,'blah');
}


So, the question is where's the error there?

I might well completely have the wrong idea about perl and what happens with
lexically scoped variables but to me that says $sth gets destroyed because it
drops out of scope, even if the first exit from the function is taken. If not
then that says all lexically scoped variables remain allocated until explicitly
destroyed. So I could have a function creating and storing huge amounts of data
in 'my' variables and that data will still be stored, adding to the processes
memory footprint, until my process exits. That could be years.


--
Nigel J. Andrews

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> So, the question is where's the error there?
>
> I might well completely have the wrong idea about perl and what happens with
> lexically scoped variables but to me that says $sth gets destroyed because it
> drops out of scope, even if the first exit from the function is taken. If not
> then that says all lexically scoped variables remain allocated until explicitly
> destroyed. So I could have a function creating and storing huge amounts of data
> in 'my' variables and that data will still be stored, adding to the processes
> memory footprint, until my process exits. That could be years.

You first assumption is correct: if a statement handle goes out of scope, it
no longer can be "Active" or "Inactive". What happens is this: when the
disconnect method is called, it polls all the current statement handles to
see if any of them are still have the Active flag set. If they do, it throws
the error you see. However, destroying a statement handle (e.g. by leaving the
scope in which its variable is declared) does not do any checking, and the
Active flag is ignored. In other words:

my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
$sth->execute("Mustard","library","wrench");
$dbh->disconnect();

This throws the error. DBI is saying: Hey! You just went through all the
trouble of making this query, don't you want to see all the results?

{
  my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
  $sth->execute("Peacock","kitchen","candlestick");
}
## $sth is gone
$dbh->disconnect();


No error is thrown in this case, as the variable $sth fails to exist at the
end of the unnamed block, so the disconnect method has no way of knowing anything
about the statement handle you created. In practice, finish() is not called
very often, as you usually want to fetch everything you asked for. It's a nice
short-circuit as in my first example, however, as it is more efficient than
calling a fetch method if you know there is nothing there.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302031426

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+PsKkvJuQZxSWSsgRArYYAKDDXvaJOerEOoHJoMtn+oxj71JzcACfSSV4
wGMsZL0nl3LUHW8Mrdn5Xu8=
=RWel
-----END PGP SIGNATURE-----

On Mon, 3 Feb 2003 greg@turnstep.com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > So, the question is where's the error there?
> >
> > I might well completely have the wrong idea about perl and what happens with
> > lexically scoped variables but to me that says $sth gets destroyed because it
> > drops out of scope, even if the first exit from the function is taken. If not
> > then that says all lexically scoped variables remain allocated until explicitly
> > destroyed. So I could have a function creating and storing huge amounts of data
> > in 'my' variables and that data will still be stored, adding to the processes
> > memory footprint, until my process exits. That could be years.
>
> You first assumption is correct: if a statement handle goes out of scope, it
> no longer can be "Active" or "Inactive". What happens is this: when the
> disconnect method is called, it polls all the current statement handles to
> see if any of them are still have the Active flag set. If they do, it throws
> the error you see.

Funny you should say that because one of the things I did to try and fix the
problem was basically that only I did a finish instead of throwing an error.

> However, destroying a statement handle (e.g. by leaving the
> scope in which its variable is declared) does not do any checking, and the
> Active flag is ignored. In other words:
>
> my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
> $sth->execute("Mustard","library","wrench");
> $dbh->disconnect();
>
> This throws the error. DBI is saying: Hey! You just went through all the
> trouble of making this query, don't you want to see all the results?
>
> {
>   my $sth = $dbh->prepare("SELECT COUNT(*) FROM mansion WHERE who=? AND room=? AND item=?");
>   $sth->execute("Peacock","kitchen","candlestick");
> }
> ## $sth is gone
> $dbh->disconnect();
>
>
> No error is thrown in this case, as the variable $sth fails to exist at the
> end of the unnamed block, so the disconnect method has no way of knowing anything
> about the statement handle you created. In practice, finish() is not called
> very often, as you usually want to fetch everything you asked for. It's a nice
> short-circuit as in my first example, however, as it is more efficient than
> calling a fetch method if you know there is nothing there.

It doesn't explain why in your previous example that I changed slightly I had
to add an explicit call to finish before returning early in order to avoid the
error being raised. Indeed in the application the only reason I have a database
connection available in the level I am commiting, which is where I came across
the problem, is to do the transaction control. All other work is done in object
methods and all statement handles are scoped within the methods only and so out
of scope at commit time.

Unfortunately, there might also have been some other effects in play that
changed as the app. changed data (in auto commit mode) as I've now reverted to
not scanning statement handles myself before disconnecting  and I've got errors
again. However, this is just one script out of two I've currently done towards
the application. Both use many of the same classes and the first does not
generate this error.

Anyway, I still think there is something very odd going on with this stuff/perl
but accept that that is how it is and am living with it, going through bloating
the code by adding finishes all over the place and being very paranoid about
where exactly I'm putting them. I will just remember for next time I use DBI
that it's got these idioscracies.

Thanks for the input on this.

--
Nigel J. Andrews

On Mon, 3 Feb 2003 14:49:39 +0000 (GMT), Nigel J. Andrews wrote:
> I presume that the issue here is that in the 1 or 0 rows returned case
> where 1 row indicates I have a problem then I am not fetching from or
> finishing that statement. Although, in the debugger I've seen the Active
> flag still set after doing a single fetch on a 1 row resultset.

The problem is that the statement doesn't get marked inactive until you have
fetched the row after the last one:

  # There are some rows to fetch here
  $sth1->execute;  # Active
  ...
  $sth1->fetch;    # Last row returned, still active
  $sth1->fetch;    # undef returned, now it's inactive

  # No rows to fetch in this case
  $sth0->execute;  # Active
  $sth0->fetch;    # undef returned, now it's inactive

As you can see, this behaviour is necessary in order that empty result sets
can be recognised. Otherwise you'd be fetching on an inactive handle.

--
    Peter Haworth    pmh@edison.ioppublishing.com
Hi, this is Ken. What's the root password?

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