Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

After spending a little time with Class::DBI, it appears that it is possible for a database handle with uncommitted transactions to go dead, a new database handle returned, and commit called on the new database handle--resulting in lost transactions of the uncommitted dead handle.

A brief example using Class::DBI's example Music/CD database may help to explain: 

for my $cd ( 1 .. 2 ) {
    Music::CD->create(
        {   cdid   => $cd,
            title  => "Title of CD $cd",
            year   => '2006',
            artist => 5,
        }
    );
    for my $track ( 1 .. 5 ) {
        Music::Track->create(
            {   trackid  => $cd . $track,
                cd       => $cd,
                position => $track,
                title    => "Title of track $track",
            }
        );

    }
}
Music::DBI->dbi_commit();

[download]
The above results in 13 calls to Ima::DBI's _mk_db_closure, which returns a database handle: 

sub _mk_db_closure {
    my ($class, @connection) = @_;
    my $dbh;
    return sub {
        unless ($dbh && $dbh->FETCH('Active') && $dbh->ping) {
            $dbh = DBI->connect_cached(@connection);
        }
        return $dbh;
    };
}

[download]
_mk_db_closure and DBI->connect_cached will return the same database handle if the dbh->ping is successful. But if it's not, a new database handle is returned.

If say, on the 8th call to _mk_db_closure, the ping fails, and a new database handle is returned. Wouldn't all uncommited transactions on the dead database handle be silently lost?

I realize that the possibility of the handle going dead, and a new one returned is remote. In practice, if the a handle goes dead, usually the database is down and a new handle won't be able to be created. However, the possibility exists.

So, is it possible that Class::DBI's mulitple potential calls to connect_cached during a single execution could result in lost transactions?

Thank you

Replies are listed 'Best First'.
Re: Class::DBI, connect_cached and possible lost transactions?
by perrin (Chancellor) on May 02, 2006 at 15:26 UTC
    Yes, it is possible, although unlikely. If you're concerned about it, the simplest thing to do is to modify that code so that it will only reconnect at the beginning of a new web request. How you do that depends a bit on your environment. (For example, stashing the connection in $r->pnotes() makes sense for mod_perl.) It's not a good idea to just never reconnect, since periods of inactivity in a web application often lead to timeouts on the database connections.
[reply]
      Perrin:

      Thank you for your response.

      You say that it's unlikely. I'm attempting to determine the likelihood of this occuring. In other words, is it worth the effort to correct the problem.

      The first scenario that comes to mind is a momentary network outage from the web server to the database server. Using my example from the original question:

      1. Immediately before the 8th call to _mk_db_closure, there is a momentary network outage to the database server.

      2. $dbh->ping is called (which on DBD::Oracle, does a 'select sysdate from dual')
      I 'think' this is an immediate, no timeout operation. ping would return false.

      3. A new connection would attempted to be made.
      With DBI->connect (on Oracle), I'm pretty sure that it tries a few times to connect before timing out and erroring. Before the timeout, the network comes back up. A new dbh is created and transactions are lost.

      Another scenario is that our primarly database server fails over to the secondary node. Depending on how quickly the fail over happened, this could be a similar situation as the momentary network outage.

      With your experience, would you agree that the above scenarios could result in the lost transaction problem?

      Thank you, Perrin.

[reply]
        I think you're over simplifyng a little. TCP/IP is involved here, so even the ping will wait for some period of time. Also, the database library (provided by Oracle in your example) may attempt to handle momentary outages. I know the MySQL one does. If the outage was just the right amount of time though, it could happen, and the same for the failover scenario.

        It's definitely safer to not try to reconnect during a web request. If you'd rather not touch Ima::DBI, you can override db_Main in your Class::DBI base class.

[reply]

Back to Seekers of Perl Wisdom