race condition in C-Adapter causes deadlock.

Hi,

I understand that ActiveRecord is threadsafe, but because the C-
adapters for both postgresql and mysql use blocking calls, it's
surprisingly easy to cause deadlock.

assume Balance is a model that tracks the balance of a bank account,
and we don't use optimistic locking.

b = Balance.find_or_create_by_id(1)
  while true
    Thread.new {
      b = Balance.find(1) # assumes account is there
      b.balance = b.balance + 1
      b.save!
      puts "."
    }
  end

this would reliably lock a process. it only happens when transaction
is used (which it is, when balance.save!). The steps leading to the
deadlock seems to be this

thread1 => begin
thread1 => update => mysql
thread1 preempted
thread2 scheduled
thread2 => begin
thread2 => update => mysql
thread2 => C read()
thread2 blocks whole process on read()
thread1 doesn't get to commit

process blocks

you can try running the test script,

ruby thread-test 1 # should be ok, because no transaction is used
ruby thread-test 2 # locks
ruby thread-test 3 # locks

File: 'thread-test.rb':

require 'rubygems'
require 'thread'
require 'activerecord'

DB = ENV["DB"] || "mysql"
`rm sql.log` if File.exist?("sql.log")
ActiveRecord::Base.logger = ::Logger.new("sql.log")
ActiveRecord::Base.establish_connection \
:adapter => DB,
#:adapter => "mysql"
#:adapter => "postgresql"
#:adapter => "jdbcmysql"
:database => "thread_test",
:username => "vp",
:password => "vp",
:socket => "/var/run/mysqld/mysqld.sock",
:host => "localhost",
:port => 5432,
:pool => 50

ActiveRecord::Schema.define(:version => 1) do
  create_table "balances", :force => true do |t|
    t.integer "balance", :default => 0
  end
end

class Balance < ActiveRecord::Base
end

b = Balance.find_or_create_by_id(2)
puts b.balance

which_test = ARGV[0].to_i || 1

case which_test
when 1
  puts "Raw update, no transaction"
  while true
    Thread.new {
      sql = <<-SQL
UPDATE balances SET balance = balance+1 WHERE id = 2
SQL
      ActiveRecord::Base.connection.execute(sql)

      puts "."
    }
  end
when 2
  puts "Update through ActiveRecord"
  while true
    Thread.new {
      b = Balance.find(2)
      b.balance = b.balance + 1
      b.save!
      puts "."
    }
  end
when 3
  puts "Raw update in transaction"
  while true
    Thread.new {
      sql = <<-SQL
UPDATE balances SET balance = balance+1 WHERE id = 2
SQL
      ActiveRecord::Base.transaction do
        ActiveRecord::Base.connection.execute(sql)
      end
      puts "."
    }
  end
end

Hi,

I understand that ActiveRecord is threadsafe, but because the C-
adapters for both postgresql and mysql use blocking calls, it's
surprisingly easy to cause deadlock.

Does it eventually undeadlock ? when mysql blocks because there's a
deadlock it should eventually give up (although of course with your
test script this would soon happen again). Deadlocks are just a risk
of life when it comes to transactions. MRI's threading model does
indeed mean that a threadsafe rails is much less useful than on
something like jruby

Fred

For PostgreSQL, set allow_concurrency: true in your
config/database.yml. It'll use the async API which sidesteps these
issues with blocking syscalls.

For MySQL, use the mysqlplus driver.

Best,
jeremy