Caiwangqin's blog

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.

标签： Technologies