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 <[email protected]> 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 > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
