Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Easysoft ODBC-ODBC Bridge

What is this problem I'm experiencing when using PHP persistent connections?

Article:
00714
Last Reviewed:
17th January 2024
Revision:
1

Please see the other questions about PHP in this section. What follows is a general list of additional issues we have found with persistent connections in PHP.

  1. Some versions of PHP (specifically 4.2.0, but we did not test them all) appear to close connections automatically when a PHP script completes, even if odbc_close() is not called. However, this only happens if odbc_connect() is used to open the connection, not when odbc_pconnect() is used. Bear this in mind when considering the next point.
  2. PHP can allow open transactions to exist over persistent connections. I.e. if you turn off autocommit with odbc_autocommit(connection, FALSE), then do some inserts/updates and forget to commit or rollback, the transaction remains in the uncommitted state for that connection. The implications of this can serious, and, at worst, may result in blocking due to database locks in your scripts. As an example, here is some code which 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 is not closed 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 issued 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
  3. This issue is not strictly to do with persistent connections, but is related to the previous point. If you repeated the code shown in the previous point, but used an odbc_connect() instead, the database connection is automatically closed by PHP. The call to SQLDisconnect will fail with an "outstanding transaction". What PHP does here is roll back the transaction and then call 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() does not not 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."
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)