Re: PDO, Persistent Connections

Philip Thompson wrote:

On Mar 28, 2006, at 10:23 AM, Jochem Maas wrote:

Jarratt Ingram wrote:

Hi, I have a little unusual question, we are currently looking into the new PDO extension with PHP5.1. We are currently use Mysql, InnoDB and transactions. What we would like to know if we use the PDO extension and persistent connections, can we start a Transaction on one page and then commit it from another separate page by carrying the PDO object through a php session? Thus preventing php from automatically closing that specific DB connection. The reason for this we use remote connections to the Database. Any thoughts or comments if i have missed the boat completely

not quite but in practice what you want is not possible. in order to continue that transaction on 'page 2' you browser will need to connect to the same thread/process as the connection of request of 'page 1' - not impossible but it's _very_ hit and miss (not something you want to build upon). oh and php resource datatypes (e.g. a DB connection) cannot be serialized. in short you don't want to to do what you think you want to do :-) instead stuff the data in the session until you have it all and only then start and finish the transaction in a single request.

Regards Jarratt

*Please don't throw tomatoes at me!!* Sounds like Java might be a solution here. From my understanding, Java can hold persistent connections. I know that may not be a feasible solution, but still something to consider.

Not in any sort of scalable fashion. In order to do this with either Java or PHP the second request has to come in on the same machine and get the same database connection as in the initial request. While you could do some sticky session tricks and some database pooling, connection holding magic, there is no way this will work at all at any sort of scale. What happens, for example, if that second request never comes? How long do you keep that half-finished transaction connection around? You obviously can't give it to anybody else, so it is in limbo. The difference between Java and PHP here is that Java has a single JVM process per machine that holds the pool of database connections, so in a single-server architecture it becomes easier to give subsequent requests the same database connection, but as far as I am concerned that just means it is easier to shoot yourself in the foot since this is really a bad way to approach this. -Rasmus