How to avoid blocking indefinitely when using a PHP script to update a DB table that has an open/pending transaction?

Question

I have found that if I modify table X via SQLplus and don't commit the change, then if my web appliation (which runs as a PHP script under Apache in ModPHP mode) attempts to commit a change to table X it will block indefinitely until I commit my changes via SQLplus.

This behavior is correct, but what I want is a clean/reliable/simple way to have my web application time out after N seconds and emit an HTTP 409 error instead of blocking indefinitely.

My first thought was to use pcntl_alarm(N) with a signal handler to catch the SIGALRM after N seconds. But I found that pcntl_* functions are generally elided from the ModPHP Apache module at compile time, presumably so as to avoid messing up the signals that the root Apache process itself uses to control its children. Maybe it ought to be harmles for PHP scripts run via ModPHP to handle their own SIGARLMs, but that's not a fight I want to pick with my Ops team right now, so I have to reject this approach.

My second thought is to fork/launch a child process every time my application needs to modify table X, and have the parent process poll the child (maybe via select() on a pipe) until the operation is done (success) or N seconds have passed (timeout + failure).

The second approach will work, but it strikes me as ugly, complicated, and fragile.

Does anyone know a better way, given the constraints of PHP version 5.2.11 and Apache version 1.3.41 (on Linux 2.6.9)?

Answer 1

I think for this situation I would try to control the query timeout directly. The best way to do that would be to use PHP's MySQLi module rather than the MySQL module, because then you have access to the mysqli::options function.

Using mysqli::options, you can set the query timeout value to whatever you want on a per-connection basis. Once it times out, you can control the error in your code as part of the normal flow as soon as it happens.

If you can't use MySQLi (or you're not using MySQL 5), you can always set this value directly in the MySQL options, but of course that will have a larger impact on your application.

Edit

In response to your comment, I can see this probably won't work. Here's something I just thought of that is clunky, but might get you by.

The set_time_limit() function can put an overall time limit on the execution of a PHP script, and if that limit is reached, a PHP Fatal Error is triggered. However, when you call it, the timer resets to zero... and PHP Fatal Errors can be handled.

You could write your own error handling function, and just before you execute the problem query, swap it into use via set_error_handler(). Immediately call set_time_limit(), and run your query. If your time limit gets exceeded, a fatal error will trigger, and go to your error handling function. You can proceed with it from there.

If it doesn't get triggered, you can reset the timer with another call to set_time_limit() right after the query, and then use restore_error_handler() to swap the default error handling function back into place.

As I said, clunky, but perhaps it might work?

Answer 2

Unfortunately, it seems that PHP does does not allow the use of pcntl_*() functions when running under ModPHP + Apache. And when running on Linux, the facilities which PHP itself provides for setting timeouts on a script apply only to the time spent in the script itself, not time spent waiting for a blocked DB query.

Therefore, you must resort to the generic strategy of launching a new process (we can't call pcntl_fork()) to "do stuff that might block", then having the parent process poll the connection and give up after N seconds. One way to do this is with proc_open(), with PHP-side pipes set to non-blocking (via stream_set_blocking()) and a stream_select() polling loop that aborts after a certain amount of time.

This is annoying on several levels. First, you have to make sure to launch the child process properly and safely pass information about the required operation from parent to child, then you have to make sure the polling logic deals properly with the various child failure conditions, then you have to safely pass information back from child to parent, and finally you have to clean everything up properly afterwards (which becomes important if the parent process persists over many iterations). The complexity here is such I'll bet almost anyone who does it for the first time will have to spend weeks fixing bugs before the behavior is really robust. And although you have explicit control over polling frequency and error handling, you also have the costs associated with launching a new process.

I grant there is no "one size fits all" solution to all problems of this type, but everyone who has written a web application in PHP that has to talk to an Oracle database has at one time or another wanted to "run query X and throw an Exception if we don't get a response back within N seconds". So it strikes me as ridiculous that everyone who wants this behavior must implement the whole system described above (or find someone else who has already done it).