Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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 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!! :)


Replies are listed 'Best First'.
Re: Global dbh and inserts
by McA (Priest) on Feb 19, 2014 at 08:23 UTC


    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

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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1075409]
Approved by davido
and the monks are chillaxin'...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2018-03-23 03:53 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (287 votes). Check out past polls.