MySQLでデッドロックしてやり直すパターン

会社で書き忘れたけど、表題のもテストしたら800req/secぐらいだった

#!/bin/env ruby                                                                                                      
# encoding: utf-8
# Author: kimoto
require 'mysql2'
require 'digest/md5'

client1 = Mysql2::Client.new(:host => "localhost", :username => "root", :password => '', :database => 'bench')
client2 = Mysql2::Client.new(:host => "localhost", :username => "root", :password => '', :database => 'bench')

client1.query("INSERT INTO users (name, amount) VALUES('kimoto', 0) ON DUPLICATE KEY UPDATE amount = 0;");
#p client1.query("select * from users").to_a
#p client1.query("select @@AUTOCOMMIT").to_a
#client1.query("SET AUTOCOMMIT=0")

tries = 10000
start = Time.now

th = Thread.new{
  tries.times{ |n|
    puts "client1-#{n}"
    begin
      client1.query("START TRANSACTION;")
      client1.query("SET @var1 = (SELECT amount FROM users WHERE name = 'kimoto');")
      client1.query("UPDATE users SET amount = @var1 + 1 WHERE name = 'kimoto';")
      client1.query("COMMIT;")
    rescue => ex
      puts ex
      if ex.message =~ /Deadlock found when trying to get lock/
        puts "deadlock found!!! try retry"
        retry
      end
    end
  }
  puts "end client1"
  client1.close
}

th2 = Thread.new{
  tries.times{ |n|
    puts "client2-#{n}"
    begin
      client2.query("START TRANSACTION;")
      client2.query("SET @var2 = (SELECT amount FROM users WHERE name = 'kimoto');")
      client2.query("UPDATE users SET amount = @var2 + 1 WHERE name = 'kimoto';")
      client2.query("COMMIT;")
    rescue => ex
      puts ex
      if ex.message =~ /Deadlock found when trying to get lock/
        puts "deadlock found!!! try retry"
        retry
      end
    end
  }
  puts "end client2"
  client2.close
}

th.join
th2.join

stop = Time.now

puts "totally end"

diff =  stop - start
puts "#{diff} seconds"

requests = (tries * 2)

puts "#{requests / diff} req/sec