Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Global dbh and inserts

by SleepyJay (Beadle)
on Feb 19, 2014 at 01:28 UTC ( #1075409=perlquestion: print w/ replies, xml ) Need Help??
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
Re: Global dbh and inserts
by McA (Curate) 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

Re: Global dbh and inserts
by sundialsvc4 (Monsignor) 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.

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1075409]
Approved by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2014-07-31 06:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (245 votes), past polls