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

Question

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

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

Most likely multiple workers try to update the same database row, and some time out while waiting for access. From your information, we can't tell much more. You should probably re-examine your code for race conditions and see where the deadlocks come from... — averell, May 17 '16 at 14:32
You may find http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded helpful — Frederick Cheung, May 19 '16 at 13:35
You may also want to read about the `innodb_lock_wait_timeout` and `innodb_deadlock_detect` variabes — Jared Beck, Aug 06 '20 at 20:18

score 0 · Answer 1 · answered May 24 '16 at 17:49

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.

score 0 · Answer 2 · edited May 23 '17 at 12:25

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:

score 0 · Answer 3 · answered May 27 '16 at 12:40

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.

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

3 Answers3