Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
You just updated your question to completely conflict with your initial question. You state in bold "If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely." You disprove this with your latest update: " it's the second transaction that times out, not the one that fell asleep." -- seriously?! –  hobodave Mar 1 '11 at 20:08
    
I suppose my phrasing could have been clearer. By "blocked indefinitely," I meant that the second transaction would time out with an error message that says "try restarting transaction"; but doing so would be fruitless, because it would just time out again. So the second transaction is blocked—it will never complete, because it will keep timing out. –  Trevor Burnham Mar 1 '11 at 20:14
    
@Trevor: Your phrasing was perfectly clear. You've simply updated your question to ask a completely different thing, which is extremely bad form. –  hobodave Mar 1 '11 at 20:28
    
The question has always been the same: It's a problem that the second transaction is blocked indefinitely when the first transaction is never committed or rolled back. I want to force a ROLLBACK on the first transaction if it takes more than n seconds to complete. Is there any way to do so? –  Trevor Burnham Mar 1 '11 at 20:52
    
@Trevor: In what circumstance is the first transaction "never committed or rolled back"? –  hobodave Mar 1 '11 at 20:54

4 Answers 4

up vote 2 down vote accepted

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 http://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection.

share|improve this answer
    
Thanks, I really appreciate the answer! –  Trevor Burnham Feb 28 '13 at 4:07

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.

share|improve this answer
    
Sorry, but while is true when the transaction is waiting for a lock (as the name and documentation of innodb_lock_wait_timeout suggest), it isn't so in the situation I posed: where the client hangs or crashes before it gets the change to do a COMMIT or ROLLBACK. –  Trevor Burnham Mar 1 '11 at 19:51
    
@Trevor: You are simply wrong. This is trivial to test on your end. I just tested it on my end. Please take back your downvote. –  hobodave Mar 1 '11 at 20:02
    
@hobo, just because your right doesn't mean he has to like it. –  Chris S Mar 1 '11 at 20:09
    
Chris, could you explain how he's right? How will the second transaction occur if no COMMIT or ROLLBACK message is sent to resolve the first transaction? Hobodave, perhaps it would be helpful if you presented your test code. –  Trevor Burnham Mar 1 '11 at 20:17
2  
@Trevor: You're not making a bit of sense. You want to serialize transactions, right? Yes, you said so in your other question and repeat that here in this question. If the first transaction has not completed, how on earth would you expect the second transaction to complete? You have explicitly told it to wait for the lock to be released on that row. Therefore, it must wait. What do you not understand about this? –  hobodave Mar 1 '11 at 20:25

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.

share|improve this answer
2  
This comment is intended for future readers of this answer. Please don't do this, even if it is the accepted answer. –  hobodave Mar 2 '11 at 4:25
    
OK, rather than downvoting and leaving a snide comment, how about explaining why this would be a bad idea? The person who posted the original PHP script said that it kept their server from hanging; if there's a better way to achieve the same goal, show me. –  Trevor Burnham Mar 2 '11 at 15:18
6  
The comment is not snide. It is a warning for all future readers to make no attempt at using your "solution". Automatically killing long running transactions is a unilaterally terrible idea. It should never be done. That is the explanation. Killing sessions should be an exception, not a norm. The root cause of your contrived problem is user error. You do not create technical solutions for DBAs who lock rows and then "walk away". You fire them. –  hobodave Mar 3 '11 at 18:32

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 http://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.

share|improve this answer
    
You have overlooked that ActiveRecord uses synchronous (blocking) I/O. All that script is doing is interrupting the Ruby sleep command. If your Foo.create command blocked for 5 minutes the Timeout would not kill it. This is incompatible with the example provided in your question. A SELECT ... FOR UPDATE could easily block for long periods of time, as could any other query. –  hobodave Mar 5 '11 at 4:01
    
It should be noted that nearly all mysql client libraries utilize blocking I/O, hence the suggestion in my answer that you would need to use or write your own that used non-blocking I/O. Here is a simple demonstration of the Timeout being useless: gist.github.com/856100 –  hobodave Mar 5 '11 at 4:17
    
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. But thanks, that's a good point. I've updated the question to note it.) –  Trevor Burnham Mar 5 '11 at 4:22
    
I cannot duplicate your claimed results. I updated the gist to utilize ActiveRecord::Base#find_by_sql: gist.github.com/856100 Again, this is because AR uses blocking I/O. –  hobodave Mar 5 '11 at 4:32
    
@hobodave Yes, that edit was erroneous and has been corrected. To be clear: The timeout method will roll back the transaction, but not until the MySQL database responds to a query. So the timeout method is suitable for preventing long transactions on the client side or long transactions that consist of several relatively short queries, but not for long transactions in which a single query is the culprit. –  Trevor Burnham Mar 5 '11 at 5:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.