Easysoft ODBC-ODBC Bridge

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

Article:
00714
Last Reviewed:
19th July 2005
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 and not when odbc_pconnect is used. You should appreciate this fact especially 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 it remains in the uncommitted state in that connection. The implications of this can be most 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 (run to MS 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 table set col2 = 'update' where col1=1");

Note that there is no odbc_close() and odbc_pconnect() is used so because of [1] above, the connection is not closed when the script completes. When the URL causing this PHP is browsed again, the chances are 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 because of the outstanding update issued by the script the first time it was run. With MS SQL Server you can verify that it is a lock by simply changing the odbc_exec call to:

$stmt = odbc_exec($dbc,
"set lock_timeout 5 update table 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] Not strictly to do with persistent connections but related to [2] above. If you repeated [2] but with an odbc_connect() then when the db 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:

[a] you should realise your transaction is being rolled back in these circumstances.

[b] even if you call odbc_close(), as it does not not return a value, you will not be aware your transaction was rolled back.

[c] this behavior is contrary to the documentation which says

"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)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.