mysql pdo transaction and session storage

Question

I have trouble deciding what would be the optimal solution for my web app, which is accessing (mainly reading) many times the same user data on every session.

Should I retrieve all user data at once (about 40 fields) to $_SESSION when opening a new session or should I keep a persistent PDO (mysql) connection and query just the parameters I need from the database on every script execution instead?

ALSO:

Would there be a big difference in performance between reading/updating a lot of fields at once (whith a custom query) or one by one (with a custom combination of generic queries) in the same transaction? e.g.

$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));

$fieldlist='';
foreach ($fields as $i=>$field){
    $fieldlist.=$field['name'].':field'.$i.',';
}
rtrim($fieldlist,',');
$dbh->prepare("UPDATE user SET ".$fieldlist." WHERE name=:name");
foreach ($fields as $i=>$field){
    $stmt->bindValue(':field'.$i, $field['value'], PDO::PARAM_STR);
}
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();

$dbh = null;

vs.

$dbh = new PDO("mysql:host=localhost;dbname",$dbuser,$dbpass,array(PDO::ATTR_PERSISTENT => true));


$dbh->beginTransaction();

foreach($fields as $field){
    $stmt=$dbh->prepare("UPDATE user SET ".$field['name']."=:field WHERE name=:name");
    $stmt->bindValue(':field', $field['value'], PDO::PARAM_STR);
    $stmt->bindValue(':name', $name, PDO::PARAM_STR);
    $stmt->execute();
}

$dbh->commit();

$dbh = null;

You cannot serialize a database connection resource into a session. I'd say that settles that debate. — deceze, Dec 20 '12 at 14:53

score 2 · Answer 1 · answered Dec 21 '12 at 09:44

2

Would there be a big difference in performance

I would say there is a difference in common sense.
Why to repeat one by one while you can do it at once? Is there any reason to write more code for the same task?
It seems you're looking for troubles not where they really are.

answered Dec 21 '12 at 09:44

Your Common Sense

156,878
40
214
345

Because, depending on the scenario (user input) I may need to modify different combinations of parameters. I'm not quite so familiar with ACID transactions yet and I would like to know just how much of a bottleneck this is compared to taking the time to optimize queries so when more than one column of a particular table is modified on the same transaction I'd join the statements in one query instead of starting a transaction, executing all of them and then commiting... – NotGaeL Dec 21 '12 at 10:47
Anyway, I imagine what you're trying to tell me is that it's not a good idea to have get/set functions to retrieve/update each parameter (something I would have never thought of before starting to read about PDO, and ACID transactions), and that's all I wanted to know, but as you can see I'm a little lost in the concepts here so I'd appreciate if you could point me out to some literature on the topic that you know of (other than PHP manual and wikipedia, my current references). Thank you very much. – NotGaeL Dec 21 '12 at 10:54

score 1 · Accepted Answer · answered Dec 20 '12 at 15:02

Cannot be done. Even if you could do this, it is a bad idea. You would create a situation with lots of concurrent open connections that would exceed your max connections.

I am not sure why you have to continually update the session with the same data. Just put the data in the session one time, and be done.

If you are dealing with a large data set, you may want to consider caching. This would reduce database load. You could use memcached. This will improve performance, it allows you to specify what and how many resources to cache.

score 0 · Answer 3 · answered Dec 20 '12 at 14:56

You should keep the data in session.

You cannot safely keep in session any handler - in your case db connection - as the identifier on the next request may not be the same. Also, keeping open connections just in case may not be the best thing to do.

If you load the data any way, just keep in session - if you query it each time, you will use at least the same amount of memory, but will perform a DB query, so performance will be worse compared to just keeping the data. If you need to refresh the data every request, just do it with a new connection, do not try to keep connection handler in the session.

mysql pdo transaction and session storage

3 Answers3