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
On Sep 18, 12:55 am, Howard Yeh <hay...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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> > 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
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 On Thu, Sep 17, 2009 at 4:55 PM, Howard Yeh <hayeah-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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 > > > > > >