ActiveRecord :: StatementInvalid: Mysql2 :: Error: timeout wait timed out

Question

In my rails project, I use sidekiq to handle a multitask task, but in sidekiq log 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

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

Answer 1

This error occurs due to a transaction timeout, when different workers try to change the same resource, mainly a database deadlock.

This happens if you use transactions explicitly as SomeModel.transaction { SomeModel.task_that_takes_too_much_time }or using the usual ActiveRecord methods, which modify records because everything is enclosed in a transaction.

, , - , , , https://github.com/mhenrixon/sidekiq-unique-jobs .perform_in.

Answer 2

, , , , - - , .

, , - .

MySQL .

SET GLOBAL innodb_lock_wait_timeout = 28800;

, , - FORCE UNLOCK MySQL:

, , , sql, .

. - , .

FORCE UNLOCK MySQL:

Answer 3

, SQL .

, .

, SQL .

, .