Multiple connections with the Bank

Question

In a PHP system that communicates with the BD (Mysql) to recover the information and returns it to the user who accesses it is necessary to establish multiple connections with the Bank? Or just one?

Answer 1

Says the PHP manual that ...

The connection to the server will be closed as soon as the execution of the script terminate, unless it has previously been closed using explicitly...

True, but not 100%. We can’t forget the server side there may be settings like Connection Lifetime or Connection pooling.

In a simplistic way an instance will correlate a connection to the database. Nowadays and from version 5 of PHP, the use of the PDO library, allows implicitly to use the management of the connection to the database, reusing the same "connection" to each HTTP Request consequential.

In this way and in the case of a simple access of an instance to a database is peaceful, but the geographical location of both points will interfere, as the time between Request.

If we go a little deeper into the issue we realize that dealing with the creation of a connection is a slow process. And that’s why instead of making a new connection for each request, the server SQL uses a POOL of, say for example, 100 "active/permanent connections".

In practice you in PHP when you need a connection, request it (instantiating the PDO) and use, and use... You can adjust the size of the POOL to change the way your application behaves. Maior POOL = mais conexões = mais "threads" allowing you to do more "things" at the same time, but this can also overload the server side.

When writing these lines comes a recent project to memory where...

An instance of the PDO object will correspond to a database connection, but multiple instances can use the same connections when implementing "jump" permanently between databases such as a Sharding, more precisely in FEDERATED TABLE. It is a very simple mechanism because the base remains where the reuse of connections is up to the server to manage, and can and must your code be a little clever and give a help.

In practice results in instances of PDO between "consecutive requets". Again and in practice it is maintained the mechanism of an instance of the "object" for a connection, but not necessarily separate connections if for example a server hosts several databases or in cases where the request uses the same server of a previous request.

The resources are not endless and that is why we call the server a server/ SQL service, because in addition to "tidy" the information, it is also in charge of many other tasks such as managing and optimizing the connections.

Answer 2

In your code only one suffices.

What may occur is your application being called multiple times by the same user or different users. It would be several instances of the application, each one will establish its own connection to the database. Then in this scenario the database will be linked to several sessions of data communication with the instances of the application.

But each instance only needs to have one connection, no matter how many different things you want to do with in the database.

This is not to say that it cannot have multiple connections. If there is a reason to have several, no problem either.

In some cases the connection remains open but in a standby state, so when the same instance requires creating a new connection that has been "closed" it takes advantage of the existing connection.

Answer 3

Only one per database. Normally you will open the connection at the beginning of the request processing, and this will be reused until the end.

Something like:

1. Start the script execution;
2. Calls the configuration script;
3. Includes other needed scripts;
4. Opens connection to the bank;
5. Executes queries;
6. Generates the request response (HTML, XML, Json, JPG, or anything else);
7. Terminates the connection;
8. End of script.

In some cases you may want the connection to be persistent between multiple requests, but I don’t know of a case where it will be useful. Check the driver documentation used to check the support for this feature.

Some driver characteristics you use may influence the connection lifecycle.

For example, the PDO driver will keep the connection open until the object destructor is called (assigning null to the reference of the object).

Other drivers such as pg will close the connection or when a call to pg_close happen or when you close script execution. Otherwise, call the pg_connect will reuse connections created, provided that the connection string is the same.

Observing: I know you tagged mysql in its question, but it is somewhat generic. I ended up quoting Postgresql because I have more experience with it.