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