Interrupting PHP script doing a large mysqli query from Javascript/AJAX

Question

I have an extremely large query to a MySQL db that takes on the order of 20s (gathering data from millions of rows). The query is run through an AJAX call which then waits for a response from the php script gathering the data.

JS query:

$.ajax({
      url: "../largeQuery.php",
      type: "POST",
      success: function (data) {
          alert(data);
      }
});

largeQuery.php:

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME); //these are set elsewhere
$largeQuery = 'I am some super long query';
$response = $mysqli->query($largeQuery);
print_r($response);

However, if a user changes the content of the page and the $response data is no longer needed, the next page is locked up because MySQL is still fetching $largeQuery and can't handle another query until it finishes $largeQuery.

I understand that I am able to get the thread id from $thread_id = $mysqli->thread_id, and then kill it by $mysqli->kill($thread_id), but how can I get JS to issue an update to currently running PHP code?

Did you check this: php.net/manual/en/function.ignore-user-abort.php ? — Dekel
– Dekel, Commented Sep 12, 2017 at 18:58
Maybe you should just fix your "large query" and make it run faster. — Paul Spiegel
– Paul Spiegel, Commented Sep 12, 2017 at 19:03
You should be able to have 4 ( or 6) concurrent connections to the server depending browser, if its locking up I suspect you have the session open, try calling session_write_close after you started the query to free it up., you should also cancel the XMLHttpRequest if its no longer needed — andrew
– andrew, Commented Sep 12, 2017 at 19:11
@andrew it's better to call session_write_close before query, because after the query session will be closed anyway, and no effect will occur. — CappY
– CappY, Commented Sep 12, 2017 at 19:14

CappY · Accepted Answer · 2017-09-12 19:18:41Z

1

MySQL is not blocking your PHP. The problem is with session. During Query, PHP will be waiting for query, and session will be waiting for PHP to finish, during that time session will be locked and PHP will NOT accept new request, until query finish, then php execution finish.

You could close session before run Query, but you must be sure not to read/write from session after that (or you could reopen it after query).

But with that scenario - you risk to flood your SQL with slow queries and slow it even more. Try to optimize it.

There is a small article on this case: http://konrness.com/php5/how-to-prevent-blocking-php-requests/

answered Sep 12, 2017 at 19:18

CappY

1,5911 gold badge17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

andrew Over a year ago

I think you can still read from the session after closing it for writing without issues

CappY Over a year ago

I think you cannot read (and you should not be able to). After you close it,some other request could change data in session, and without reopen the session, you could read old data.

JDog131 Over a year ago

Thanks, that article helped a lot!

CappY Over a year ago

@andrew after some testing - you can actually read $_SESSION, even write to it (of corse - data is not actually written in session). That weird. Should not happen, but that's the way PHP work. :)

andrew Over a year ago

@CappY my understanding is you can write to $_SESSION['whatever'] or read back from it at anypoint, but the value will not survive a page reload unless the session was started and open for writing

Collectives™ on Stack Overflow

Interrupting PHP script doing a large mysqli query from Javascript/AJAX

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related