Setting a time limit for a transaction in MySQL/InnoDB

Question

This sprang from this related question, where I wanted to know how to force two transactions to occur sequentially in a trivial case (where both are operating on only a single row). I got an answer—use SELECT ... FOR UPDATE as the first line of both transactions—but this leads to a problem: If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely. The innodb_lock_wait_timeout variable sets the number of seconds after which the client trying to make the second transaction would be told "Sorry, try again"... but as far as I can tell, they'd be trying again until the next server reboot. So:

1. Surely there must be a way to force a ROLLBACK if a transaction is taking forever? Must I resort to using a daemon to kill such transactions, and if so, what would such a daemon look like?
2. If a connection is killed by wait_timeout or interactive_timeout mid-transaction, is the transaction rolled back? Is there a way to test this from the console?

Clarification: innodb_lock_wait_timeout sets the number of seconds that a transaction will wait for a lock to be released before giving up; what I want is a way of forcing a lock to be released.

Update 1: Here's a simple example that demonstrates why innodb_lock_wait_timeout is not sufficient to ensure that the second transaction is not blocked by the first:

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

With the default setting of innodb_lock_wait_timeout = 50, this transaction completes without errors after 55 seconds. And if you add an UPDATE before the SLEEP line, then initiate a second transaction from another client that tries to SELECT ... FOR UPDATE the same row, it's the second transaction that times out, not the one that fell asleep.

What I'm looking for is a way to force an end to this transaction's restful slumber.

Update 2: In response to to hobodave's concerns about how realistic the example above is, here's an alternate scenario: A DBA connects to a live server and runs

START TRANSACTION
SELECT ... FOR UPDATE

where the second line locks up a row that the application frequently writes to. Then the DBA is interrupted and walks away, forgetting to end the transaction. The application grinds to a halt until the row is unlocked. I'd like to minimize the time that the application is stuck as a result of this mistake.

Answer 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.

Answer 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.

Answer 3

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

Answer 4

In a similar situation my team decided to use pt-kill (https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html). It can report on or kill queries that (among other things) are running for too long. It's then possible to run it in a cron every X minutes.

Problem was that a query that had an unexpectedly long (e.g. indefinite) execution time locked a backup procedure that tried to flush tables with read lock, which in turn locked out all other queries on "waiting for tables to flush" which then never timed out because they're not waiting for a lock, which resulted with no errors in the application, which ultimately resulted with no alerts to administrators and application halting.

The fix was obviously to fix the initial query, but in order to avoid the situation accidentally happening in the future, it would be great if it was possible to auto-kill (or report on) transactions/queries that last longer than specific time, which author of the question seems to be asking. This is doable with pt-kill.

Answer 5

A simple solution will be to have a stored procedure to kill the queries that take more time than your required timeout time and use innodb_rollback_on_timeout option along with it.

Answer 6

After Googling around for a while, it looks like there's no direct way to set a per-transaction time limit. Here's the best solution I was able to find:

The command

SHOW PROCESSLIST;

gives you a table with all of the commands currently being run and the time (in seconds) since they started. When a client has stopped giving commands, then they're described as giving a Sleep command, with the Time column being the number of seconds since their last command completed. So you could manually go in and KILL everything that has a Time value over, say, 5 seconds if you're confident that no query should take more than 5 seconds on your database. For instance, if the process with id 3 has a value of 12 in its Time column, you could do

KILL 3;

The documentation for the KILL syntax suggests that a transaction being carried out by the thread with that id would be rolled back (though I haven't tested this, so be cautious).

But how to automate this and kill all overtime scripts every 5 seconds? The first comment on that same page gives us a hint, but the code is in PHP; it seems that we can't do a SELECT on SHOW PROCESSLIST from within the MySQL client. Still, supposing we have a PHP daemon that we run every $MAX_TIME seconds, it might look something like this:

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
  $process_id=$row["Id"];
    if ($row["Time"] > $MAX_TIME) {
      $sql="KILL $process_id";
      mysql_query($sql);
  }
}

Note that this would have the side effect of forcing all idle client connections to reconnect, not just those that are misbehaving.

If anyone has a better answer, I'd love to hear it.

Edit: There is at least one serious risk of using KILL in this way. Suppose that you use the script above to KILL every connection that's idle for 10 seconds. After a connection has been idle for 10 seconds, but before the KILL is issued, the user whose connection is being killed issues a START TRANSACTION command. They're then KILLed. They send an UPDATE and then, thinking twice, issue a ROLLBACK. However, because the KILL rolled back their original transaction, the update went through immediately and can't be rolled back! See this post for a test case.

Answer 7

As hobodave suggested in a comment, it is possible in some clients (though not, apparently, the mysql command-line utility) to set a time limit for a transaction. Here's a demonstration using ActiveRecord in Ruby:

require 'rubygems'
require 'timeout'
require 'active_record'

Timeout::timeout(5) {
  Foo.transaction do
    Foo.create(:name => 'Bar')
    sleep 10
  end
}

In this example, the transaction times out after 5 seconds and gets automatically rolled back. (Update in response to hobodave's comment: If the database takes more than 5 seconds to respond, the transaction will be rolled back as soon as it does—not sooner.) If you wanted to ensure that all of your transactions time out after n seconds, you could build a wrapper around ActiveRecord. I'm guessing that this also applies to the most popular libraries in Java, .NET, Python, etc., but I haven't tested them yet. (If you have, please post a comment on this answer.)

Transactions in ActiveRecord also have the advantage of being safe if a KILL is issued, unlike transactions done from the command line. See https://dba.stackexchange.com/questions/1561/mysql-client-believes-theyre-in-a-transaction-gets-killed-wreaks-havoc.

It does not appear to be possible to enforce a maximum transaction time on the server side, except through a script like the one I posted in my other answer.