テスト環境は以下のようになります。
トランザクションを使用しますので、テストはInnoDBで行っています。
最初はThreadでやっていました。しかし、特定のクエリーでスクリプトが無反応になり、 そのまま数分待っていると途中終了するようになりました。 そこでログを調べてみるとデッドロックが発生しているようでした。 テストの目的はトランザクション分離レベルにより動作確認で、Thread、forkのどちらでも良いためforkに変更しました。 ちなみにPostgreSQLではThreadでもforkでも同じ結果になります。
テストスクリプトは以下のようになります。
require './MyMysql'
TABLE_MYISAM = 'test_myisam'
TABLE_INNODB = 'test_innodb'
PROCESS_MAX = 5
TEST_COUNT_MAX = 200
def countup(max, title, table_name, autocommit)
print("\n", title, "\n")
tp = MyMysql.new(table_name)
tp.db_init()
1.upto(PROCESS_MAX) { |index|
#Thread.new {
fork {
testdb = MyMysql.new(table_name, 'fork')
testdb.query(index, max, autocommit) { |num, i, conn|
yield num, i, conn
}
}
}
#sleep 5
ret = Process.waitall
end
def test_mysql_myisam()
test_mysql(TABLE_MYISAM)
end
def test_mysql_innodb()
test_mysql(TABLE_INNODB)
end
def test_mysql(table_name)
max = TEST_COUNT_MAX
title = "UPDATE #{table_name} SET count=count+1 WHERE id=1;"
autocommit = true
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("UPDATE #{table_name} SET count=count+1 WHERE id=1;")
}
title = "SELECT count AS current FROM #{table_name} WHERE id=1;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;"
autocommit = true
countup(max, title, table_name, autocommit) { |num, i, conn|
res = conn.query("SELECT count FROM #{table_name} WHERE id=1;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
}
#-----------------
title = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1 FOR UPDATE;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1 FOR UPDATE;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1 FOR UPDATE;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1 FOR UPDATE;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1 FOR UPDATE;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1 FOR UPDATE;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1 FOR UPDATE;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1 FOR UPDATE;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
#-----------
title = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n" +
"BEGIN\n" +
"SELECT count AS current FROM #{table_name} WHERE id=1;\n" +
"UPDATE #{table_name} SET count=1+current WHERE id=1;\n" +
"COMMIT"
autotcommit = false
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
conn.query("BEGIN;")
res = conn.query("SELECT count FROM #{table_name} WHERE id=1;")
row = res.fetch_row
conn.query("UPDATE #{table_name} SET count=1+" + row[0] + " WHERE id=1;")
conn.query("COMMIT;");
}
title = "UPDATE #{table_name} SET count=1+(SELECT count FROM #{table_name} WHERE id=1) WHERE id=1;"
autocommit = true
countup(max, title, table_name, autocommit) { |num, i, conn|
conn.query("UPDATE #{table_name} SET count=1+(SELECT count FROM #{table_name} WHERE id=1) WHERE id=1;")
}
end
#test_mysql_myisam()
test_mysql_innodb()
exit
require "mysql"
require "pp"
# create table test_myisam(id integer, name varchar(32), count integer);
# create table test_innodb(id integer, name varchar(32), count integer);
class MyMysql
attr_accessor :table_name
attr_accessor :exec_name
def initialize(table_name=nil, exec_name="")
@table_name = table_name
@exec_name = exec_name
end
def opendb()
begin
return Mysql.new('localhost', 'root', 'password', 'testdb')
rescue => ex
print("MyMysql.opendb ERROR ", ex.class, " ", ex.message,"\n")
return nil
end
end
def db_init()
tblname = @table_name
conn = opendb()
conn.query("DELETE FROM #{tblname};")
conn.query("INSERT INTO #{tblname}(id,name,count) VALUES(1,'MySQL',0);")
conn.close
end
def query(num, count_max, autocommit=false)
begin
tblname = @table_name
conn = opendb()
#print("conn:",num," ")
#pp conn
conn.autocommit(autocommit)
i = 0
while (i < count_max)
i += 1
yield num, i, conn
end
conn.autocommit(true)
res = conn.query("SELECT count FROM #{tblname};")
print_result(num, res)
rescue => ex
print("MyMysql.query ERROR ", ex.class, " ", ex.message,"\n")
ensure
conn.close if conn
end
end
def print_result(num, res)
if ((row = res.fetch_row) != nil)
print(@exec_name, num, ": count = ", row[0], "\n")
else
print("EROOR get count\n")
end
end
end
forkでプロセスを5個作成しています。 それぞれが200まで加算していきますので、最後のプロセスのcountが1000になれば正常にカウントされたことになります。 1000に満たない場合はカウント漏れが発生しています。
テストでは以下のようにテストスクリプトのtest_mysql.rbを実行しました。
$ ruby test_mysql.rb
$ ruby test_mysql.rb UPDATE test_innodb SET count=count+1 WHERE id=1; fork1: count = 353 fork2: count = 580 fork3: count = 761 fork4: count = 997 fork5: count = 1000 SELECT count AS current FROM test_innodb WHERE id=1; UPDATE test_innodb SET count=1+current WHERE id=1; fork1: count = 274 fork2: count = 403 fork3: count = 519 fork4: count = 601 fork5: count = 691 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN SELECT count AS current FROM test_innodb WHERE id=1 FOR UPDATE; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction fork1: count = 208 SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN SELECT count AS current FROM test_innodb WHERE id=1 FOR UPDATE; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction fork5: count = 212 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN SELECT count AS current FROM test_innodb WHERE id=1 FOR UPDATE; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT fork1: count = 632 fork2: count = 741 fork3: count = 769 fork4: count = 968 fork5: count = 1000 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN SELECT count AS current FROM test_innodb WHERE id=1 FOR UPDATE; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT fork1: count = 856 fork3: count = 937 fork5: count = 978 fork2: count = 991 fork4: count = 1000 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN SELECT count AS current FROM test_innodb WHERE id=1; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT fork2: count = 485 fork5: count = 547 fork4: count = 554 fork1: count = 595 fork3: count = 618 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN SELECT count AS current FROM test_innodb WHERE id=1; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT fork2: count = 553 fork1: count = 556 fork5: count = 699 fork3: count = 701 fork4: count = 741 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN SELECT count AS current FROM test_innodb WHERE id=1; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT fork3: count = 460 fork1: count = 470 fork2: count = 526 fork5: count = 582 fork4: count = 622 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN SELECT count AS current FROM test_innodb WHERE id=1; UPDATE test_innodb SET count=1+current WHERE id=1; COMMIT MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction MyMysql.query ERROR Mysql::Error Deadlock found when trying to get lock; try restarting transaction fork1: count = 248 UPDATE test_innodb SET count=1+(SELECT count FROM test_innodb WHERE id=1) WHERE id=1; MyMysql.query ERROR Mysql::Error You can't specify target table 'test_innodb' for update in FROM clause MyMysql.query ERROR Mysql::Error You can't specify target table 'test_innodb' for update in FROM clause MyMysql.query ERROR Mysql::Error You can't specify target table 'test_innodb' for update in FROM clause MyMysql.query ERROR Mysql::Error You can't specify target table 'test_innodb' for update in FROM clause MyMysql.query ERROR Mysql::Error You can't specify target table 'test_innodb' for update in FROM clause