MySQL - Clear lock from Rails console

Owned by Arnaud Lachaume (Unlicensed)
Last updated: Feb 24, 2017 by CamilleCrespeau (Unlicensed)

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 Symptoms

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:

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 - Clearing the lock from the Rails console

You can perform the following in order to kill the offending process:

# 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)