使用PHP脚本更新具有打开/挂起事务的数据库表时，如何避免无限期阻塞？

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

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