Redmine

I should you provide more information, but i dont know which... I think the hint is in upgrade Redmine from 1.9.x to 2.0.3. Others systems are same.

deleting issue: ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT id FROM `issues` WHERE (`lft` >= 1) FOR UPDATE

Petr Pospisil wrote:

I should you provide more information, but i dont know which...

Told you, blocking transactions. Because the SQL you've been writing down here is the blocked one, not the blocking one.

Try to execute a SHOW ENGINE INNODB STATUS\G as soon as the issue occurs again?

Try increase max pool size.
http://stackoverflow.com/questions/3966215/how-to-increase-max-pool-size-in-activerecord

Petr Pospisil wrote:

SHOW ENGINE INNODB STATUS attached

Not sure but you might have to create a innodb_lock_monitor table teporarily to get lock information by issuing the SHOW ENGINE INNODB STATUS command.

As I said, I'm not a MySQL expert.

You can identify long running requests:

• enable request logging and use request-log-analyzer
• set a value to config.active_record.auto_explain_threshold_in_seconds parameter in your config/environment/production.rb so that they automatically get an explain plan in log

BTW, by default there should not be such "FOR UPDATE" part at the end of statement unless specified in code and there's no such thing.
Could that be a tuned parameter or a plugin behavior?

Nobody uses FOR UPDATE statement. But the problem seems to be solved. We bought better server and we changed ruby to 1.9.3... :o)

Petr Pospisil wrote:

Nobody uses FOR UPDATE statement. But the problem seems to be solved. We bought better server and we changed ruby to 1.9.3... :o)

Did you change adapter from mysql to mysql2 too?

Of course. The original adapter mysql is not compatible with ruby 1.9.3.
There are other benefits - Rails 3.x.y is much faster with new Ruby. E.g. /issues - up to 4 times!
(And FreeBSD were replaced by Debian)

Petr Pospisil wrote:

Of course. The original adapter mysql is not compatible with ruby 1.9.3.

And do you still have FOR UPDATE clause appended to the end of the SELECT statement?

Nop. As I wrote earlier. After upgrading HW and migrating to mysql2 and ruby 193 the locks not occured yet.

related to Defect #8143 Problem to update subtask.

I am also having the same issue - it is caused with large parent-child hierarchies and the consequent cascading updates of status, priority and other checks.

Environment:
  Redmine version                2.6.0.stable
  Ruby version                   1.9.3-p484 (2013-11-22) [x86_64-linux]
  Rails version                  3.2.19
  Environment                    production
  Database adapter               Mysql2