CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordlockingtimeout
modulaaron
modulaaron

Reputation: 2906

ActiveRecord "Mysql::Error: Lock wait timeout exceeded" with no apparent locks

Rails version: 2.3.8

Many times thoughout the day, my application will seemingly randomly return a 500 error with a corresponding entry in the production log:

ActiveRecord::StatementInvalid (Mysql::Error: Lock wait timeout exceeded; try restarting transaction: INSERT INTO `forum_posts` (`forum_topic_id`, `created_at`, `body`, `ancestry`, `updated_at`, `quote_limit`, `user_id`, `ancestry_depth`, `quote_root`) VALUES(1224783, '2011-01-24 19:18:38', 'Post body', '1285704', '2011-01-24 19:18:38', 1, 57931, 1, 1))

Inspecting the MySQL slow query log shows this entry as:

# Time: 110124 11:19:29
# User@Host: db_user[db_user] @ localhost []
# Query_time: 51  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET insert_id=0;
INSERT INTO `forum_posts` (`forum_topic_id`, `created_at`, `body`, `ancestry`, `updated_at`, `quote_limit`, `user_id`, `ancestry_depth`, `quote_root`) VALUES(1224783, '2011-01-24 19:18:38', 'Post body', '1285704', '2011-01-24 19:18:38', 1, 57931, 1, 1);

According to the Rails log, ActiveRecord returned an error because of a lock wait timeout. The long-running nature of this simple query seems to suggest so as well. The thing is, nowhere is the slow query log can I find an actual query that should require a long time to process - they are all similar to the above example. Plus, in this same log, not one entry has a Lock_time value of greater than 0.

Does anyone here have an idea as to what could be causing this apparent lock and how to isolate it? The current tools I'm using don't seem to be helping much.

Thanks in advance.

Upvotes: 3

Views: 4224

Answers (1)

Kaiser Leo
Kaiser Leo

Reputation: 91

Maybe this helps: http://www.mysqlperformanceblog.com/2007/02/25/pitfalls-of-converting-to-innodb/

We often recommend to our clients to convert their current database from MyISAM tables to InnoDB. The transfer by itself in most cases is almost plain, however the application can be broken by new unexpected errors 1205 (ER_LOCK_WAIT_TIMEOUT) Lock wait timeout expired. Transaction was rolled back. 1213 (ER_LOCK_DEADLOCK) Transaction deadlock. You should rerun the transaction.

It is not hard to handle these errors, but you should be aware of. This is some thing we do in our PHP applications:

class mysqlx extends mysqli {

...

  function deadlock_query($query) {
          $MAX_ATTEMPS = 100;
          $current = 0;
          while ($current++ < $MAX_ATTEMPS) {

                  $res = $this->query($query);

                  if(!$res && ( $this->errno== '1205' || $this->errno == '1213'  ) )
                                  continue;
                  else 
                          break;
             }
 } 
...
}

You may want to handle ER_LOCK_WAIT_TIMEOUT in different way, especially for web applications where long waiting is not good, you get the idea.

Upvotes: 4

Related Questions