Rollback the transaction when user abort the connection

Question

First of all, sorry for my english, I wrote this with some help of Google Translate.

I'm trying to make an application like Google Wave with PHP and Ajax. I have a textarea that when the user input something, the javascript on the page detected with oninput and send the contents of the textarea to the server and the server stores the contents into the database.

What I'm doing is that every time when i send the content by XHR, there is XHR.abort() that always interrupts the previous XHR request. The data that is in the database are fine, however, sometimes it is stored a previous version.

I know it happens because PHP has not stopped the execution even though the client has made an abort and sometimes the previous request has taken more time that the last request and completed after the last request, so I read the manual of functions of "ignore_user_abort" and "connection_aborted", but the problem persist.

I created this script to simulate the situation and I hoped when I aborted the connection (press 'stop', close the tab/window), there are not any new data on the database, but after 5 seconds, there still I have new data, so I need help to rollback the transaction when user abort the connection.

Here is the script to simulate (PDO_DSN, PDO_USER, PDO_PASS are defined):

<?php
ignore_user_abort(true);

ob_start('ob_gzhandler');

$PDO = new PDO(PDO_DSN, PDO_USER, PDO_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

$PDO->beginTransaction();
$query = $PDO->query('INSERT INTO `table` (`content`) VALUES (' . $PDO->quote('test') . ')');
sleep(5);
echo ' ';
ob_flush();
flush();
if (connection_aborted()) {
  $PDO->rollBack();
  exit;
}
$PDO->commit();

ob_end_flush();

Answer 1

If you are finding XHR.abort() and connection_aborted() unreliable, consider other ways to send an out-of-band signal to inform the running PHP request that it should not commit the transaction.

Are you running APC (or could you be)?

Instead of invoking XHR.abort(), you could send another XHR request signaling the abort. The purpose of this request would be to record a special key in the APC user cache. This key's presence would indicate to the running PHP request that it should roll back.

To make this work, each XHR request would need to carry a (relatively) unique transaction identifier, e.g. as a form variable. This identifier would be generated randomly, or based on the current time, and would be sent in the initial XHR as well as the "abort" XHR and would allow the abort request to be correlated to the running request. In the below example, the transaction identifier is in form variable t.

Example "abort" XHR handler:

<?php
$uniqueTransactionId = $_REQUEST['t'];
$abortApcKey = 'abortTrans_' . $uniqueTransactionId;

apc_store($uniqueTransactionId, 1, 15);

Example revised database write XHR handler:

<?php
$PDO = new PDO(PDO_DSN, PDO_USER, PDO_PASS,
               array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

$PDO->beginTransaction();
$query = $PDO->query('INSERT INTO `table` (`content`) VALUES (' . $PDO->quote('test') . ')');

$uniqueTransactionId = $_REQUEST['t'];
$abortApcKey = 'abortTrans_' . $uniqueTransactionId;
if (apc_exists($abortApcKey)) {
  $PDO->rollBack();
  exit;
}

$PDO->commit();

You may still have timing issues. The abort may still arrive too late to stop the commit. To deal with this gracefully, you could modify the database write handler to record an APC key indicating that the transaction had committed. The abort handler could then check for this key's existence, and send back a meaningful XHR abort result to advise the client, "sorry, I was too late."

Keep in mind, if your application is hosted on multiple live servers, you will want to use a shared cache such as memcached or redis, since APC's cache is only shared across processes on a single machine.

Answer 2

How about having the browser send back a timestamp or a running number that you also store in the database. and your update can check so that it only writes if the new timestamp is newer.