there is something wrong with Innodb transactions, i found it from rails production log below:
ActiveRecord::StatementInvalid (Mysql::Error: Lock wait timeout exceeded; try restarting transaction: UPDATE articles SET `created_at` = ‘2008-04-23 17:22:42′, `rating_count` = 0, `votes` = 0, `title` = ‘P1派对衣着榜4月第2周’, `content_text` = ‘’, `award_id` = NULL, `status` = 0, `category_id` = 1, `image` = NULL, `user_id` = 18098, `score` = 0.0, `description` = ‘’, `layout_id` = NULL, `imagethumb` = NULL, `sequence` = 100, `attachment_id` = NULL, `counts` = 0, `city_id` = 1, `counts_24h` = 0 WHERE id = 910):
i have tried to resolve this problem for many hours, but can not found any directions, someone suggest me change InnoDB to MyISAM, so i changed MySQL table engine from InnoDB to MyISAM. do more testing…
alter table articles engine=MyISAM;
I have done this progress for resolve this problem:
- check mysql slow log , lot’s of sql go into mysql slow log, cause we used many derived tables in query, it seems derived tables can not use index(Derived Tables and Views Performance). but the query time is fast, almost all query execute in 0.0x sec.
long_query_time = 2
log_slow_queries = ON
- stop batch update sql statement, like update online
- improve Update code and create progress
if you already fixed this problem, please let me know. thanks.
Tags:
四月 25th, 2008 at 12:32 pm
That may not be a good approach for solve this problem. By using MyISAM, you lose the ability for transaction. Furthermore, MyISAM only supports table-level locking, while InnoDB supports row-level locking. In some cases, you may encounter more lock timeout in MyISAM.
Back to your specific problem, it is mostly caused by a slow transaction making other transactions timeout. How could a slow transaction happen? Rails adds transaction automatically in any model object saving. The transaction includes all the filters like before_save, after_save, etc. Anything in these filters may make the transaction slow. Check these filters and filters of associations may be a good first step to solve this problem. Slow query log may or may not help much because any sql in such slow transaction may be very fast.