PHP script aborts, causes MySQL transaction to fail, whole app locks for a little while

Question

I'm using transactions in a MySQL database, along with Ajax, and I'm having some troubles with scripts timing out. So, here's a rundown of what happens:

1. Ajax script on page makes request to server.
2. Server receives request; script starts a MySQL transaction.
3. Ajax script is set to timeout after two seconds; times out; tells server to abort request.
4. Server script dies; transaction is left hanging.
5. MySQL notices transaction has been left hanging; rolls back as it's supposed to, but not before a bunch of customers become less than happy because the tables were locked for half a minute and they couldn't access the website.

What to do?

I imagine the server script just needs a bit more time than the two seconds it's given, so I called ignore_user_abort() on every page, and then called register_shutdown_function() to explicitly roll back a transaction if the client has in fact aborted.

Is this a good plan? Is there an alternative?

Answer 1

register_shutdown_function() by itself would even be a good solution. Try removing the ignore_user_abort() so that the script knows when (or if) the users aborts the loading.

I would also probably let the script have more than 2 seconds of a time out, see if this helps.

Answer 2

Are you using persistent database connections, ie: using mysql_pconnect()? Those will keep the connection alive within the web server even when no PHP script is running. As such, any transactions/locks acquired during the script's run will be left active within that persistent connection when the script shuts down abnormally.

Even if your scripts normally clean up after themselves with mysql_close(), any abnormal termination will leave that persistent connection up. And stay up until the connection is re-used from the pool and properly shut down by some other script.