Setting a time limit for a transaction in MySQL/InnoDB

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.

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.

We want to kill transactions that are idle and are blocking the table(s) that I want to use.

It's easy enough to kill the thread that has the lock, in the example from the original question the lock was on a single table where no changes had been made so it would be fast but be careful if the killed transaction had been running for hours and had loads of data to rollback it will take some time to complete the rollback before you can use it.

interactive_timeout -- will kill sleeping connections including the ones with an open transaction; it will also kill all connections with no transactions that are just sleeping.

innodb_rollback_on_timeout - does not do exactly what it sounds like (InnoDB will always rollback something on a time out if there is something to rollback -- this controls how much (see the documentation)

So let's be surgical and only kill the threads that need killing.

show processlist will not help you because it only shows you who's sleeping but not who has any kind of lock but this query will (this is MySQL 8 might work in older versions, I didn't try):

SELECT i.trx_mysql_thread_id thread, 
    i.trx_state, 
    trx_tables_in_use tbl_used, 
    trx_tables_locked tbl_locked, 
    p.USER, 
    p.DB, 
    p.COMMAND, 
    p.TIME, 
    TIMESTAMPDIFF(SECOND, trx_started, now()) trx_time, 
    SUBSTRING(trx_query, 1, 40) 
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID;

Notice - the columns TIME and trx_time -- the first is want you see in the processlist and the second is how long this transaction has been running. Do not mistake them, if TIME is very low, 1 or 2 seconds, then this transaction appears to be doing something. Also notice the trx_tables_in_use and trx_tables_locked tbl_locked columns if these are 0 this transaction will not block another query -- But is will block things like ALTER TABLE that require a "metadata lock". Finally notice trx_state when you try this it will likely be "RUNNING" after you kill the transaction it could be "ROLLBACK" and you will still have to wait.

Now changing the above query to find only the thread_id(s) you'd like to kill:

SELECT i.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID
WHERE i.trx_state = 'RUNNING'    
    AND trx_tables_locked > 0
    AND p.COMMAND = 'Sleep'
    AND p.TIME > 60

Loop through the results and kill those threads. You can add condition on the other tables too (only kill this user, only in that DB, ...) You can do this in a stored procedure called by the event manager or just have your code do this before it runs (your user may need enhanced permission - I believe you can kill your own user).

Good Luck, Frank