'ActiveRecord::StatementInvalid: Mysql2::Error: Lock wait timeout exceeded

In my rails project, I use sidekiq processing time consuming task, but in sidekiq log an error:

ActiveRecord::StatementInvalid: Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: UPDATE `marker_layers` SET `show_fields` = 'title,desc', `sort_col` = 'title,desc', `updated_at` = '2016-05-17 07:36:02' WHERE `marker_layers`.`id` = 16021210
Processor: iZ23edse84Z:29310

enter image description here sidekiq.yml

# Options here can still be overridden by cmd line args.
#   setsid sidekiq -d -C config/sidekiq.yml -e production
---
:concurrency: 5
:pidfile: tmp/pids/sidekiq.pid
:logfile: log/sidekiq.log
staging:
  :concurrency: 10
production:
  :concurrency: 40
:queues:
  - ['critical', 3]
  - ['default', 2]
  - ['low', 1]

database.yml

production:
   adapter: mysql2
   encoding: utf8mb4
   collation: utf8mb4_bin
   reconnect: false
   database: database_name
   pool: 48
   username: password
   password: password
   host: locahost
mysqlruby-on-railsactiverecordsidekiq


Solution 1:[1]

This error happens because of a transaction timeout when different workers are trying to modify the same resource, basically a database deadlock.

It happens either if you are using transactions explicitly like SomeModel.transaction { SomeModel.task_that_takes_too_much_time } or by using normal ActiveRecord methods that modify records because everything is wrapped into a transaction.

The only recommendation I can give you is to explore alternatives to make your workers unique, like by using https://github.com/mhenrixon/sidekiq-unique-jobs and making your jobs to use .perform_in.

Solution 2:[2]

That's mean the record on operating have been locked by another slow SQL, and have waiting for a long time.

Maybe that's many long transactions in your code.

Check you code, optimize slow SQL and split the long transaction.

I hope this can be helpful to you.

Solution 3:[3]

This happens when database size grow and you are explicitly doing a lot of transactions, probably some other thread is holding a record lock on some record for too long, and your thread is being timed out.

One solution I've used, is to extend the wait timeout.

Login to MySQL through terminal and run this.

SET GLOBAL innodb_lock_wait_timeout = 28800;

Another thing you can do is FORCE UNLOCK for locked tables in MySQL:

Breaking locks like this often cause atomicity in the database to not be enforced on the sql statements that caused the lock.

This is a hack. The proper solution is to fix your application that caused the locks.

FORCE UNLOCK for locked tables in MySQL:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mario Carrion
Solution 2 rainstop3
Solution 3 Community

Related Questions