ActiveRecord "Mysql :: Error: timeout wait wait wait wait wait" without visible locks

Question

Rails Version: 2.3.8

Many times during the day, my application seems to accidentally return 500 errors with the 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))

Checking 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 due to a timeout waiting for a lock. It seems that the long-term nature of this simple request also offers. The fact is that nowhere is the slow query log able to find the actual query, which should take a lot of time to process - they are all similar to the above example. In addition, in the same journal, no record has a value Lock_timegreater than 0.

- , ? , , , .

.

Answer 1

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

MyISAM InnoDB. , 1205 (ER_LOCK_WAIT_TIMEOUT) . . 1213 (ER_LOCK_DEADLOCK) . .

, . , PHP-:

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;
             }
 } 
...
}

ER_LOCK_WAIT_TIMEOUT -, -, , .