Global dbh and inserts

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

Suppose in my startup.pl for my Apache server I created a global variable, $dbh, and set that to a DBI MySQL database connection (or an Apache::DBI connection if that is better).

Later, a request comes in that runs code that does an insert using that $dbh. Given that I made $dbh global, is last_insert_id (in whichever flavor) valid whatsoever? AFAIK, it is only safe per session, which in this case makes it not safe, correct?

IOW, I would need to create a whole new connection to the database for each request to Apache that wants to insert rows? Or maybe something about how I set up transactions/commits would help me here? These are currently MyISAM tables, but I suppose they don't have to be.

Am I thinking this out correctly? Please someone check my sanity!! :)

Thanks

Comment on Global dbh and inserts

Replies are listed 'Best First'.
Re: Global dbh and inserts by McA (Priest) on Feb 19, 2014 at 08:23 UTC
Hi, it depends on the worker model you use with Apache. When you use the forking model than every child of the Apache master process should create a database handle as every request gets handled by one child. The request->do-something on the database->return-response-cycle should be safe. In this scenario you only have to be sure that each cycle is terminated by a correct commit or rollback. It would be bad if you open a transaction with one request and close it with another. When you use the threading model in Apache it gets IMHO more problematic. You have to synchronize the access to a global DBI handle and you have to be sure (as above) that ONE transaction is not used by several requests. I just looked at the docs of Apache::DBI explaining the same in more detail. Best regards McA	[reply]
Re: Global dbh and inserts by locked_user sundialsvc4 (Abbot) on Feb 19, 2014 at 14:13 UTC
I would recommend that things like `last_insert_id` be used only immediately after a statement that inserted something, and that the pair of statements be wrapped in an SQL transaction with an appropriate isolation level. This kind of highly-temporal value must not be maintained in any sort of global variable. If you do that, you will create a “random, un-reproducible, yet very fatal” bug of the very, very worst kind.
Re^2: Global dbh and inserts by SleepyJay (Beadle) on Feb 19, 2014 at 16:23 UTC
Exactly what I was thinking, yes. Thanks guys! Sounds like I'm not insane (about this stuff, at least). I think I need to understand the Apache::DBI features better. Thanks	[reply]