44
PHP Connection Pooling and High Availability
$r = oci_execute($s, OCI_NO_AUTO_COMMIT); // no commit
...
// BAD: no commit or rollback done
// 2. Continue database operations on same credentials
$c = oci_pconnect('myuser', 'mypassword', 'salespool');
$s = oci_parse($c, 'insert into mytab values (2)');
$r = oci_execute($s, OCI_NO_AUTO_COMMIT); // no commit
// Intend to commit both 1 & 2 but behavior could be random
oci_commit($c);
If there was a node or network failure just prior to point 2, the first transaction could be lost.
The second connection command may return a new, valid connection if a ping (see
oci8.ping_interval) occurs to validate the connection, and the script might not be aware that
only the second part of the transaction is committed.
The script should do an explicit commit or rollback before the second connect, or simply
continue to use the original connection. It should always do appropriate error handling.
LOGON and LOGOFF Triggers with DRCP
LOGON triggers are useful for setting session attributes needed by each PHP connection. For
example a trigger could be used to execute an ALTER SESSION statement to set a date format.
The LOGON trigger will execute when oci_pconnect() first creates the session, and the
session will be reused by subsequent persistent connections. Scripts save time by no longer
always executing code to set the date format.
The suggested practice with DRCP is to use LOGON triggers only for setting session
attributes and not for executing per PHP-connection logic such as custom logon auditing. This
recommendation is also true for persistent connections with dedicated or shared servers.
Database actions that must be performed exactly once per OCI8 connection call should be
explicitly executed in the PHP script.
From Oracle 11gR2 onwards, LOGOFF triggers fire for pooled servers when sessions are
terminated. For oci_connect() and oci_new_connect() connections, this is with oci_close()
or at the end of the script. For oci_pconnect() connections, it can happen when the pooled
server process naturally terminates or its session needs to be recreated.
It is not possible to depend on triggers for tracking PHP OCI8 connect calls. The caching,
pooling, timing out and recreation of sessions and connections with or without DRCP can
distort any record. With pooled servers, LOGON triggers can fire at authentication and when
the session is created, in effect firing twice for the initial connection.
Changing Passwords with DRCP Connections
In general, PHP applications that change passwords should avoid using oci_pconnect(). This
call will use the old password to match an open connection in PHP’s persistent connection
cache without requiring re- authentication to the database with the new password. This can
cause confusion over which password to connect with since if there is no cached connection it
is the new password that must be used. With DRCP, there is a further limitation - connections
cannot be used to change passwords programmatically. PHP scripts that use
oci_password_change() should continue to use dedicated or shared servers.
270
