How to stop PHP and SQL execution when user disconnects?

Question

I have a quite heavy SQL search query that takes a few minutes to complete, called from a PHP script that's called by an ajax request.

Problem is, users often click the search button many times from impatience, and each time it creates a new ajax request, and a new PHP script execution. But the old ones continue executing, even though the connection has been dropped. This causes the SQL server load to constantly be at 100% CPU usage.

So I tested my theory that script execution continues even after the browser tab is closed. I used 2 types of queries, an ad hoc query and a stored procedure execution, both methods do the same thing, inserting the numbers 1-9 into a table, with a 2 second delay between each number.

Ad hoc query:

for($i = 1; $i < 10; $i++){
    $sql = "INSERT INTO t (i) VALUES(?)";
    $res = pdoQuery($sql, array($i));
    if($res === false){
        echo $pdo->getErrorMessage();
        http_response_code(500);
        exit();
    }
    sleep(2);
}

SP call:

$sql = "EXEC sp_Slow";
$res = pdoQuery($sql);
if($res === false){
    echo $pdo->getErrorMessage();
    http_response_code(500);
    exit();
}

How I tested: using buttons that trigger ajax calls to each script, I tested them, by clicking the button and immediately closing the tab. And then monitoring the data in the table. And just as I suspected, new data gets inserted every 2 seconds. (This also happens if I directly open the script in the browser and closing the tab, instead of requesting it through ajax)

I need a way to completely kill both PHP and SQL execution whenever the user disconnects, transactions are not important because it's just a select operation.

Perhaps either disable the button after the first click and show one of those spinning twidder images to let them know something is happening or take a page from the playbook of any travel site and redirect them to another page that says "performing search" while they are waiting. — Jonathan Kuhn, May 30 '17 at 17:19
I'm interested in seeing if this is possible. But an alternative is to use a session variable to prevent a user from searching multiple times. — RToyo, May 30 '17 at 17:20
That only covers a particular scenario (multiple clicks of the same query), because the user sometimes legitimately needs to revise their search query, and waiting > 1 minute for the first query to finish is just poor UX — iceman2992, May 30 '17 at 17:21
@iceman2992 If that's the case, you could look into only allowing one query to be run per session. If you grab the SPID of the query being executed and save that to a session variable, you can then do a check for a value in that session variable and `kill` the query related to that SPID. — RToyo, May 30 '17 at 17:24
@iceman2992 See the answer to [this MSDN question](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c74502f1-0540-4700-9711-6b2f341d78ec/possible-to-kill-a-running-query-from-php?forum=sqldriverforphp). Basically just query `select @@spid` at the start of the script to get the SPID. Save that to your session variable. Then execute `kill [spid]` to cancel that query. I don't know which library you're using to make your queries, or if your library supports kill (my TSQL work is done outside of PHP, so I'm not familiar with its libraries), but this is what I would look into. — RToyo, May 30 '17 at 17:29
Killing the SPID only temporarily fixes the problem (while allowing others to persist). I'm with @JonathanKuhn on doing *something* to prevent the user from sending multiple requests... give them a way to cancel the processes versus "each time they resubmit their previous query is killed". Using this logic... a user would never know what they were doing was wrong or what the underlying problem is. — S3S, May 30 '17 at 18:39
You're focusing on the **symptom** and not the **cause**. Since you noticed the search is slow and that people hammer the search button - what did you do to make the search faster? — N.B., May 30 '17 at 19:10

score 0 · Answer 1 · answered May 30 '17 at 17:20

0

You can change this behaviour using php.ini directive or at runtime with ignore_user_abort() function.

answered May 30 '17 at 17:20

kopaty4

2,236
4
26
39

Can a PHP script executed via an AJAX call determine if a user has aborted the execution of the script? I believe that the OP's main concern is halting the execution of his query as well. – RToyo May 30 '17 at 17:21
Just tested it, doesn't work, I added `ignore_user_abort(false);` at the top of the script, and the same behavior was observed – iceman2992 May 30 '17 at 17:23
@iceman2992 Look here: https://stackoverflow.com/questions/7582485/kill-mysql-query-on-user-abort – kopaty4 May 30 '17 at 17:26
OP appears to be using sql server, not mysql. – Jonathan Kuhn May 30 '17 at 17:28
@JonathanKuhn the logic is the same though. You basically get the process ID from the server, and then kill that process. SQL Server works the same way as MySQL in that regard. __edit:__ My bad; I should have thought to look further than the first answer. – RToyo May 30 '17 at 17:31
@RobbieToyota The answer in the post uses a native mysql feature where it runs a query as async in the background and locks itself into a while loop checking the status of the query on each loop (and sleeping 0.5 seconds). If the user aborts, it tries to kill the query. I don't know if sql server supports any form of async/background query (been 10 years since I've worked with sql server), but if it does, then that would be a viable answer. Not to mention [pdo doesn't support async](https://stackoverflow.com/questions/35409298/how-to-use-async-mysql-query-with-php-pdo). – Jonathan Kuhn May 30 '17 at 17:49

score 0 · Answer 2 · answered May 31 '17 at 17:27

0

Here's what I did, from the comment by @Robbie Toyota, thanks!

if(!empty($_SESSION['SearchSPID'])){
    $sql = "KILL " . $_SESSION['SearchSPID'];
    $res = pdoQuery($sql);
    if($res === false){
        exit('Query error: failed to kill existing spid:' . $_SESSION['SearchSPID']);
    }       
    $_SESSION['SearchSPID'] = null;
}

$sql = "SELECT @@spid AS SPID";
$res = pdoQuery($sql);
$spid = $res->row_assoc()['SPID'];

$_SESSION['SearchSPID'] = $spid;

// do long query here

$_SESSION['SearchSPID'] = null;

Of course using this method you have to be careful about session file locking, which if happens will make this whole thing pointless, because then the requests will be sequential and not parallel

answered May 31 '17 at 17:27

iceman2992

305
2
10

Thanks for posting your solution for posterity. Does it work? I'm just curious to know if PDO supports `kill` for sql server. Also, as @N.B. mentioned, you should review your search query to speed it up. If you're using indexes properly, and you're not returning/processing massive amounts of data, a query shouldn't be taking several minutes. – RToyo May 31 '17 at 18:53
Yes it does work, and yes, I am trying to optimize the query as well. – iceman2992 Jun 05 '17 at 02:36

How to stop PHP and SQL execution when user disconnects?

2 Answers2