Project

General

Profile

Actions

Defect #11678

closed

MySQL locks and Redmine collapsing

Added by Petr Pospisil over 11 years ago. Updated almost 9 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Duplicate
Affected version:

Description

Hello

Our heavily used Redmine often dies during update or create a new issue:

ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT `issues`.* FROM `issues` WHERE `issues`.`root_id` IS NULL ORDER BY `rgt` desc LIMIT 1 FOR UPDATE

Ruby 1.8.7, MySql 5.5.4, Redmine 2.0.3, OS FreeBSD, WebServer is Passenger. Every 5 minutes is running rake redmine:email:receive_pop3.

No errors in production.log.

It is imposible to simulate this behaviour, but it occurs if more than 5-10 peoples are working. We had no problems on older Redmine < 2.0.

It's frustrating...


Files

innodb.txt (5.61 KB) innodb.txt Petr Pospisil, 2012-08-22 17:24

Related issues

Is duplicate of Redmine - Defect #6579: Tree hierachy being currupted on multiple submissions of an issueClosedJean-Philippe Lang2010-10-05

Actions
Actions #1

Updated by Etienne Massip over 11 years ago

  • Category set to Database

Is there a way you could give more information about the existing blocking transaction when the problem occurs?

I'm not a MySQL expert, maybe http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html#innodb-standard-monitor could be of some help (for InnoDB).

Actions #2

Updated by Petr Pospisil over 11 years ago

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.

Actions #3

Updated by Petr Pospisil over 11 years ago

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

Actions #4

Updated by Etienne Massip over 11 years ago

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?

Actions #6

Updated by Petr Pospisil over 11 years ago

SHOW ENGINE INNODB STATUS attached

Added to database.yml:
pool: 20
wait_timeout: 30

Actions #7

Updated by Etienne Massip over 11 years ago

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.

Actions #8

Updated by Etienne Massip over 11 years ago

You can identify long running requests:

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?

Actions #9

Updated by Petr Pospisil over 11 years ago

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)

Actions #10

Updated by Etienne Massip over 11 years ago

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?

Actions #11

Updated by Petr Pospisil over 11 years ago

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)

Actions #12

Updated by Etienne Massip over 11 years ago

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?

Actions #13

Updated by Petr Pospisil over 11 years ago

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

Actions #14

Updated by @ go2null almost 9 years ago

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
Actions #15

Updated by Toshi MARUYAMA almost 9 years ago

  • Status changed from New to Closed
  • Resolution set to Duplicate

This is fixed by #6579 on 3.0.0.
But there are new problems of MySQL 5.6 and 5.7 (#17460, #19344).

Actions #16

Updated by Toshi MARUYAMA almost 9 years ago

  • Is duplicate of Defect #6579: Tree hierachy being currupted on multiple submissions of an issue added
Actions

Also available in: Atom PDF