Setting a time limit for a transaction in MySQL/InnoDB

Solution 1:

More than half this thread seems to be about how to ask questions on ServerFault. I think the question makes sense and is pretty simple: How do you automatically roll back a stalled transaction?

One solution, if you're willing to kill the whole connection, is to set wait_timeout/interactive_timeout. See https://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection.

Solution 2:

Since your question is asked here on ServerFault it is logical to assume that you are seeking a MySQL solution to a MySQL problem, particularly in the realm of knowledge that a system administrator and/or a DBA would have expertise in. As such, the following section addresses your questions:

If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely

No, it won't. I think you're not understanding innodb_lock_wait_timeout. It does exactly what you need.

It will return with an error as stated in the manual:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

• By definition, this is not indefinite. If your application is reconnecting and blocking repeatedly then your application is "blocking indefinitely", not the transaction. The second transaction blocks very definitely for innodb_lock_wait_timeout seconds.

By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.

If you want automatic rollback, that is also explained in the manual:

The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option.

---

RE: Your numerous updates and comments

First, you have stated in your comments that you "meant" to say that you want a way to timeout the first transaction that is blocking indefinitely. This is not apparent from your original question and conflicts with "If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely".

Nonetheless, I can answer that question as well. The MySQL protocol does not have a "query timeout". This means that you cannot timeout the first blocked transaction. You must wait until it is finished, or kill the session. When the session is killed the server will automatically roll the transaction back.

The only other alternative would be to use or write a mysql library which utilizes non-blocking I/O which would permit your application to kill the thread/fork making the query after N seconds. The implementation and usage of such a library are beyond the scope of ServerFault. This is an appropriate question for StackOverflow.

Secondly, you've stated the following in your comments:

I was actually more concerned in my question with a scenario in which the client app hangs (say, gets caught in an infinite loop) during the course of a transaction than with one in which the transaction takes a long time on MySQL's end.

This was not at all apparent in your original question, and still is not. This could only be discerned after you shared this rather important tidbit in the comment.

If this is actually the problem you are trying to solve, then I'm afraid you have asked it on the wrong forum. You have described an application level programming problem which requires a programming solution, one which MySQL cannot provide, and is outside of the scope of this community. Your latest answer answers the question "How do I prevent a Ruby program from infinitely looping?". That question is off-topic for this community and should be asked on StackOverflow.