Re^2: Class::DBI, connect_cached and possible lost transactions?

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.

Comment on Re^2: Class::DBI, connect_cached and possible lost transactions?

Replies are listed 'Best First'.
Re^3: Class::DBI, connect_cached and possible lost transactions? by perrin (Chancellor) on May 03, 2006 at 21:46 UTC
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]
Re^4: Class::DBI, connect_cached and possible lost transactions? by Anonymous Monk on May 05, 2006 at 14:52 UTC
Perrin: Thank you for your ensight. Please allow me ask another question. From reading your posts on various forums, it appears that your environment is similar to the environment I work in. Are you concerned about this possibilty of lost transactions? Do you override Ima::DBI's _mk_dbi_closure, or Class::DBI's db_Main in the transaction based applications you develop and support to ensure that connect is called just once per request, for mod_perl and/or plain mod_cgi? Thank you, Perrin.	[reply]
Re^5: Class::DBI, connect_cached and possible lost transactions? by perrin (Chancellor) on May 05, 2006 at 21:30 UTC
I haven't dealt with it because I consider it very unlikely. Most failover systems are not fast enough to be an issue (i.e. the reconnect attempt will fail because the new db is not up yet), and the MySQL client libraries handle momentary outages pretty well. It would be a good idea, but it's the least of my worries really.	[reply]