Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
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 (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.

      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 taking refuge in the Monastery: (13)
As of 2014-09-23 14:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (223 votes), past polls