I'm having problems with persistent connections in PHP. What could be the cause

• Some versions of PHP (for example, 4.2.0) appear to close connections automatically when a PHP script completes, even if odbc_close() is not called. However, this only happens if odbc_connect() opens the connection, not odbc_pconnect(). Bear this in mind when considering the next point.
• PHP can allow open transactions to exist over persistent connections. For example, if you turn off autocommit with odbc_autocommit(connection, FALSE), then do some INSERTs or UPDATEs and forget to COMMIT or ROLLBACK, the transaction remains in an uncommitted state for that connection. The implications of this can be serious, and may result in database locks. As an example, here's some code that works the first time PHP is run and blocks the second time. We tested the code against SQL Server, although it could happen with other databases, depending on their locking strategy:

  $dbc = odbc_pconnect("db", "user", "password");
  odbc_autocommit($dbc, FALSE); $stmt = odbc_exec($dbc, "UPDATE mytable SET col2 = 'update' WHERE col1=1");

  Note that there is no odbc_close() and odbc_pconnect() is used. For the reasons described in the previous point, the connection does not close when the script completes. When the URL that executes this PHP is browsed again, the chances are that the web server will hand the request off to a different web server process, which executes the same PHP but blocks on the odbc_exec() call. This is because of the outstanding UPDATE run by the script the first time it was run. With SQL Server, you can verify that it is a lock by changing the odbc_exec call to:

  $stmt = odbc_exec($dbc,"SET lock_timeout 5 UPDATE mytable SET col2 = 'update' WHERE col1=1");

  which returns:

  Warning: SQL error:
  [unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]
  Lock request time out period exceeded.,
  SQL state 37000 in SQLExecDirect in script.phtml on line nn

• This issue is not strictly to do with persistent connections, but is related to the previous point. If you repeat the code shown in the previous point, but use an odbc_connect() instead, PHP automatically closes the database connection. The SQLDisconnect call fails with an "outstanding transaction" error. PHP now rolls back the transaction and then calls SQLDisconnect again. In fairness, this is probably the only thing it can do but:
  • You should realise your transaction will be rolled back in these circumstances.
  • Even if you do call odbc_close(), you will not be aware that your transaction was rolled back, because odbc_close() doesn't return a value.
  • This behaviour is contrary to the documentation, which states: "odbc_close will fail if there are open transactions on this connection. The connection will remain open in this case."