Should PDO::ATTR_PERSISTENT be used every time? [duplicate]

This question already has answ开发者_高级运维ers here: What are the disadvantages of using persistent connection in PDO (8 answers) Closed 7 years ago.

When establishing a connection to a database using PDO, should the PDO attribute PDO::ATTR_PERSISTENT be used every time? It says that this creates a persistant connection for that user, and will grab that same connection instead of re-establishing a new one each time you ask for a database connection. Why isn't this the default? Is there any reason not to use it?

---

If you don't handle transaction correctly, it can lead to a "new" persistent connection already in a transaction, which can cause chaos.

Just one simple case caused by the following code:

<?php

$pdo = getCustomPersistantPDO();
$pdo->beginTransaction();
if( rand() % 2 === 0 ) {
    //simulate a poorly handled error
    exit();
}
$pdo->commit();

?>

Request 1:

(starts w/o a transaction open)
openTransaction
incorrectly handled error
(never closes transaction)

Request 2:

(start w/ a transaction open, because it was not closed in the previous connection.)
openTransaction -> fails due to already open

BTW the correct version of example is:

<?php

$pdo = getCustomPersistantPDO();
$pdo->beginTransaction();
if( rand() % 2 === 0 ) {
    //simulate a correctly handled error
    $pdo->rollBack();
    exit();
}
$pdo->commit();

?>

---

The issue with persistant connections is that the number of connections available to MySQL is limited. If something goes wrong and that connection isn't closed, the server is going to leave it open for a long time. If the server runs out of connections, then every single application tied to it is going to be unavailable until someone intervenes.

You can probably expect something to go wrong from time to time, and under the wrong circumstances the problem of resource over-usage can leak into months if not noticed, leaving you with a very gradual degrade in performance and increase in system utilization over time (all for no gain).

Here is one good article that can help you. It focuses on MySQL, but most of the same thoughts can be generalized across the spectrum of DBMS's.

Are PHP persistent connections evil ?