In PDO, a connection to the database can be persistent or non-persistent. Persistent connections make using the attribute PDO::ATTR_PERSISTENT. Persistent connections do not close when the script ends, but they are cached and can be reused when a requested persistent connection is identical to the existing connection.
When a connection is requested, PHP checks if the connection already exists or not, if exists then it is used for execution. It affects the execution time of requests, it is faster than a non-persistent connection.
But there are some disadvantages of persistent connections also. According to the php manual documentation
If the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the http server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does
Approach 1: Example of persistent connection: We already discussed that Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. Because of persistent connection cached, it is beneficial to avoid the overhead of establishing a new connection. There is no need to establish a connection every time to the database, and the result is fast query optimization.
PHP
<?php$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array( PDO::ATTR_PERSISTENT => true));?> |
Approach 2: Example of non-persistent connection: With a successful persistent connection to the database, an instance of the object of the PDO class is returned to your script. That’s why the connection with the PDO object remains active for the long period. If you want to close this connection, you need to delete references to the object and destroy it. To do this, you want to assign null value to the object. If you don’t want to make any changes, a connection will close when your script ends automatically.
PHP
<?php$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);// Use the connection here$sth = $dbh->query('SELECT * FROM foo');// And now we're done; close it$sth = null;$dbh = null;?> |
Advantages of persistent connections:
- Connections are not close when the script ends i.e. no need to establish connections again.
- If the connection is identical then the request will execute faster than a non-persistent connection.
- A persistent connection is time-consuming and secure.
Disadvantages of persistent connections: In PHP, persistent connections are having some major limitations as follows:
- Persistent connections may occur server-wide deadlocks and also have database errors during page generation. It also affects transactions, table locks, temporary tables, session variables, etc. features of MySQL are in danger.
- Persistent connections may be hit the limit because they occupy hundreds of MySQL sockets and threads.
- When for a certain reason the connection or script is dead in the middle of a transaction, may occur table locks until the deadlock timer kicks in, since the deadlock timer can kill all the newest requests instead of older requests.
- Suppose there is a group of web servers and one of them is slow web server have a backup and consumes a number of persistent connections, then those connections cannot be used by other servers and not be able to complete their requests.
Conclusion: These are some disadvantages of persistent connections. But disabling persistent connections may affect on the execution time of requests.
