This article explains how to detect and resolve database locks created by a rails application. The steps relate to MySQL but could be adapted for any other backend (e.g. Postgres).
'
1 - Typical Incident
Due to long running locks (e.g.: lock while ssh-ing a compute rack which is unavailable), you may one day end up with the following error while trying to save a record:
| Code Block |
|---|
| language | ruby |
|---|
| title | Lock wait timeout exceeded; try restarting transaction |
|---|
|
2.1.0 :017 > c.save
(0.6ms) BEGIN
(50943.5ms) UPDATE `compute_racks` SET `status` = 'stopping', `updated_at` = '2015-04-12 08:31:45' WHERE `compute_racks`.`id` = 97
(0.5ms) ROLLBACK
ActiveRecord::StatementInvalid: Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: UPDATE `compute_racks` SET `status` = 'stopping', `updated_at` = '2015-04-12 08:31:45' WHERE `compute_racks`.`id` = 97
from /apps/mcluster/releases/20150312091323/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.19/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'
from /apps/mcluster/releases/20150312091323/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.19/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `block in execute'
from /apps/mcluster/releases/20150312091323/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.19/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
from /apps/mcluster/releases/20150312091323/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.19/lib/active_support/notifications/instrumenter.rb:20:in `instrument' |
This means that the record is currently locked by a MySQL process.
2 - Resolution
You can perform the following in order to kill the offending process:
| Code Block |
|---|
|
# First, acquire a mysql connection manually
connection = ActiveRecord::Base.connection
# Display the full list of processes
puts connection.execute("SHOW PROCESSLIST").map { |e| e.join(' | ') }
# This will return something like this
# PID | User | Host | User | Work | Time | Current Query
# 49907 | maestrano | localhost | maestrano | Query | 132 | |
# 49908 | maestrano | localhost | maestrano | Sleep | 1354 | |
# 49909 | maestrano | localhost | maestrano | Sleep | 2744 | |
# The tricky part is to detect which process is the offending one. Usually, you will be looking
# for long running ones ( time > 10000) or the one currently performing an UPDATE query (Current Query column)
# Once you have identified the process, kill it via PID
connection.execute("KILL 49907")
# Test that you can save the blocked record
my_blocked_record.touch
# Finally, release the connection back into the connection pool
ActiveRecord::Base.connection_pool.checkin(connection) |