The mysqli Extension and Persistent Connections

The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.

Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.

The problem with persistent connections is that they can be left in unpredictable states by clients. For example, a table lock might be activated before a client terminates unexpectedly. A new client process reusing this persistent connection will get the connection as is. Any cleanup would need to be done by the new client process before it could make good use of the persistent connection, increasing the burden on the programmer.

The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:

  • Rollback active transactions

  • Close and drop temporary tables

  • Unlock tables

  • Reset session variables

  • Close prepared statements (always happens with PHP)

  • Close handler

  • Release locks acquired with GET_LOCK()

This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.

The mysqli extension does this cleanup by automatically calling the C-API function mysql_change_user().

The automatic cleanup feature has advantages and disadvantages though. The advantage is that the programmer no longer needs to worry about adding cleanup code, as it is called automatically. However, the disadvantage is that the code could potentially be a little slower, as the code to perform the cleanup needs to run each time a connection is returned from the connection pool.

It is possible to switch off the automatic cleanup code, by compiling PHP with MYSQLI_NO_CHANGE_USER_ON_PCONNECT defined.

Note:

The mysqli extension supports persistent connections when using either MySQL Native Driver or MySQL Client Library.

Here you can write a comment


Please enter at least 10 characters.
Loading... Please wait.
* Pflichtangabe
There are no comments available yet.

PHP cURL Tutorial: Using cURL to Make HTTP Requests

cURL is a powerful PHP extension that allows you to communicate with different servers using various protocols, including HTTP, HTTPS, FTP, and more. ...

TheMax

Autor : TheMax
Category: PHP-Tutorials

Midjourney Tutorial - Instructions for beginners

There is an informative video about Midjourney, the tool for creating digital images using artificial intelligence, entitled "Midjourney tutorial in German - instructions for beginners" ...

Mike94

Autor : Mike94
Category: KI Tutorials

Basics of views in MySQL

Views in a MySQL database offer the option of creating a virtual table based on the result of an SQL query. This virtual table can be queried like a normal table without changing the underlying data. ...

admin

Autor : admin
Category: mySQL-Tutorials

Publish a tutorial

Share your knowledge with other developers worldwide

Share your knowledge with other developers worldwide

You are a professional in your field and want to share your knowledge, then sign up now and share it with our PHP community

learn more

Publish a tutorial