Perlでは合計16桁以上の小数は丸められてしまうようだ。以下、いろいろ試した結果、最後の桁の数値が5では切り捨て、6では切り上げになるようだ。
$ perl -e '
> $tmp = 100.9999999999999;
> print $tmp."\n";
> '
101
$ perl -e '
$tmp = 100.999999999999;
print $tmp."\n";
'
100.999999999999
$ perl -e '
$tmp = 1000.999999999999;
print $tmp."\n";
'
1001
$ perl -e '
$tmp = 1000.99999999999;
print $tmp."\n";
'
1000.99999999999
$ perl -e '
$tmp = 1000.999999999995;
print $tmp."\n";
'
1000.99999999999
$ perl -e '
$tmp = 1000.999999999996;
print $tmp."\n";
'
1001
2015年3月5日木曜日
2015年3月3日火曜日
[Perl]POSIXのfloorを使って小数点切り捨ては実装しないほうがいい
POSIXのfloor関数を使って小数点の切り捨てロジックを実装していたら、不可解な挙動を確認したので掲載をしておく。
以下のコードはインプットされた数字を小数点2桁までで切り捨てるロジックを実装し、0.01から10.00まで0.01づつインクリメントさせながらテストをしたものである。
use strict;
use warnings;
use POSIX qw(floor);
use Test::More;
for (1..1000) {
my $num = $_ / 100;
is((floor($num*100))/100, $num, "$_/100 = $num case");
}
done_testing();
テスト結果
$ prove test.t
test.t .. 1/?
# Failed test '29/100 = 0.29 case'
# at test.t line 8.
# got: '0.28'
# expected: '0.29'
# Failed test '57/100 = 0.57 case'
# at test.t line 8.
# got: '0.56'
# expected: '0.57'
# Failed test '58/100 = 0.58 case'
# at test.t line 8.
# got: '0.57'
# expected: '0.58'
# Failed test '113/100 = 1.13 case'
# at test.t line 8.
# got: '1.12'
# expected: '1.13'
# Failed test '114/100 = 1.14 case'
# at test.t line 8.
# got: '1.13'
# expected: '1.14'
# Failed test '115/100 = 1.15 case'
# at test.t line 8.
# got: '1.14'
# expected: '1.15'
# Failed test '116/100 = 1.16 case'
# at test.t line 8.
# got: '1.15'
# expected: '1.16'
# Failed test '201/100 = 2.01 case'
# at test.t line 8.
# got: '2'
# expected: '2.01'
# Failed test '203/100 = 2.03 case'
# at test.t line 8.
# got: '2.02'
# expected: '2.03'
# Failed test '205/100 = 2.05 case'
# at test.t line 8.
# got: '2.04'
# expected: '2.05'
# Failed test '207/100 = 2.07 case'
# at test.t line 8.
# got: '2.06'
# expected: '2.07'
# Failed test '226/100 = 2.26 case'
# at test.t line 8.
# got: '2.25'
# expected: '2.26'
# Failed test '228/100 = 2.28 case'
# at test.t line 8.
# got: '2.27'
# expected: '2.28'
# Failed test '230/100 = 2.3 case'
# at test.t line 8.
# got: '2.29'
# expected: '2.3'
# Failed test '232/100 = 2.32 case'
# at test.t line 8.
# got: '2.31'
# expected: '2.32'
# Failed test '251/100 = 2.51 case'
# at test.t line 8.
# got: '2.5'
# expected: '2.51'
# Failed test '253/100 = 2.53 case'
# at test.t line 8.
# got: '2.52'
# expected: '2.53'
# Failed test '255/100 = 2.55 case'
# at test.t line 8.
# got: '2.54'
# expected: '2.55'
# Failed test '402/100 = 4.02 case'
# at test.t line 8.
# got: '4.01'
# expected: '4.02'
# Failed test '406/100 = 4.06 case'
# at test.t line 8.
# got: '4.05'
# expected: '4.06'
# Failed test '410/100 = 4.1 case'
# at test.t line 8.
# got: '4.09'
# expected: '4.1'
# Failed test '414/100 = 4.14 case'
# at test.t line 8.
# got: '4.13'
# expected: '4.14'
# Failed test '427/100 = 4.27 case'
# at test.t line 8.
# got: '4.26'
# expected: '4.27'
# Failed test '431/100 = 4.31 case'
# at test.t line 8.
# got: '4.3'
# expected: '4.31'
# Failed test '435/100 = 4.35 case'
# at test.t line 8.
# got: '4.34'
# expected: '4.35'
# Failed test '439/100 = 4.39 case'
# at test.t line 8.
# got: '4.38'
# expected: '4.39'
# Failed test '452/100 = 4.52 case'
# at test.t line 8.
# got: '4.51'
# expected: '4.52'
# Failed test '456/100 = 4.56 case'
# at test.t line 8.
# got: '4.55'
# expected: '4.56'
# Failed test '460/100 = 4.6 case'
# at test.t line 8.
# got: '4.59'
# expected: '4.6'
# Failed test '464/100 = 4.64 case'
# at test.t line 8.
# got: '4.63'
# expected: '4.64'
# Failed test '477/100 = 4.77 case'
# at test.t line 8.
# got: '4.76'
# expected: '4.77'
# Failed test '481/100 = 4.81 case'
# at test.t line 8.
# got: '4.8'
# expected: '4.81'
# Failed test '485/100 = 4.85 case'
# at test.t line 8.
# got: '4.84'
# expected: '4.85'
# Failed test '489/100 = 4.89 case'
# at test.t line 8.
# got: '4.88'
# expected: '4.89'
# Failed test '502/100 = 5.02 case'
# at test.t line 8.
# got: '5.01'
# expected: '5.02'
# Failed test '506/100 = 5.06 case'
# at test.t line 8.
# got: '5.05'
# expected: '5.06'
# Failed test '510/100 = 5.1 case'
# at test.t line 8.
# got: '5.09'
# expected: '5.1'
# Failed test '803/100 = 8.03 case'
# at test.t line 8.
# got: '8.02'
# expected: '8.03'
# Failed test '804/100 = 8.04 case'
# at test.t line 8.
# got: '8.03'
# expected: '8.04'
# Failed test '812/100 = 8.12 case'
# at test.t line 8.
# got: '8.11'
# expected: '8.12'
# Failed test '820/100 = 8.2 case'
# at test.t line 8.
# got: '8.19'
# expected: '8.2'
# Failed test '828/100 = 8.28 case'
# at test.t line 8.
# got: '8.27'
# expected: '8.28'
# Failed test '829/100 = 8.29 case'
# at test.t line 8.
# got: '8.28'
# expected: '8.29'
# Failed test '837/100 = 8.37 case'
# at test.t line 8.
# got: '8.36'
# expected: '8.37'
# Failed test '845/100 = 8.45 case'
# at test.t line 8.
# got: '8.44'
# expected: '8.45'
# Failed test '853/100 = 8.53 case'
# at test.t line 8.
# got: '8.52'
# expected: '8.53'
# Failed test '854/100 = 8.54 case'
# at test.t line 8.
# got: '8.53'
# expected: '8.54'
# Failed test '862/100 = 8.62 case'
# at test.t line 8.
# got: '8.61'
# expected: '8.62'
# Failed test '870/100 = 8.7 case'
# at test.t line 8.
# got: '8.69'
# expected: '8.7'
# Failed test '878/100 = 8.78 case'
# at test.t line 8.
# got: '8.77'
# expected: '8.78'
# Failed test '879/100 = 8.79 case'
# at test.t line 8.
# got: '8.78'
# expected: '8.79'
# Failed test '887/100 = 8.87 case'
# at test.t line 8.
# got: '8.86'
# expected: '8.87'
# Failed test '895/100 = 8.95 case'
# at test.t line 8.
# got: '8.94'
# expected: '8.95'
# Failed test '903/100 = 9.03 case'
# at test.t line 8.
# got: '9.02'
# expected: '9.03'
# Failed test '904/100 = 9.04 case'
# at test.t line 8.
# got: '9.03'
# expected: '9.04'
# Failed test '912/100 = 9.12 case'
# at test.t line 8.
# got: '9.11'
# expected: '9.12'
# Failed test '920/100 = 9.2 case'
# at test.t line 8.
# got: '9.19'
# expected: '9.2'
# Failed test '928/100 = 9.28 case'
# at test.t line 8.
# got: '9.27'
# expected: '9.28'
# Failed test '929/100 = 9.29 case'
# at test.t line 8.
# got: '9.28'
# expected: '9.29'
# Failed test '937/100 = 9.37 case'
# at test.t line 8.
# got: '9.36'
# expected: '9.37'
# Failed test '945/100 = 9.45 case'
# at test.t line 8.
# got: '9.44'
# expected: '9.45'
# Failed test '953/100 = 9.53 case'
# at test.t line 8.
# got: '9.52'
# expected: '9.53'
# Failed test '954/100 = 9.54 case'
# at test.t line 8.
# got: '9.53'
# expected: '9.54'
# Failed test '962/100 = 9.62 case'
# at test.t line 8.
# got: '9.61'
# expected: '9.62'
# Failed test '970/100 = 9.7 case'
# at test.t line 8.
# got: '9.69'
# expected: '9.7'
# Failed test '978/100 = 9.78 case'
# at test.t line 8.
# got: '9.77'
# expected: '9.78'
# Failed test '979/100 = 9.79 case'
# at test.t line 8.
# got: '9.78'
# expected: '9.79'
# Failed test '987/100 = 9.87 case'
# at test.t line 8.
# got: '9.86'
# expected: '9.87'
# Failed test '995/100 = 9.95 case'
# at test.t line 8.
# got: '9.94'
# expected: '9.95'
# Looks like you failed 69 tests of 1000.
test.t .. Dubious, test returned 69 (wstat 17664, 0x4500)
Failed 69/1000 subtests
Test Summary Report
-------------------
test.t (Wstat: 17664 Tests: 1000 Failed: 69)
Failed tests: 29, 57-58, 113-116, 201, 203, 205, 207
226, 228, 230, 232, 251, 253, 255, 402
406, 410, 414, 427, 431, 435, 439, 452
456, 460, 464, 477, 481, 485, 489, 502
506, 510, 803-804, 812, 820, 828-829, 837
845, 853-854, 862, 870, 878-879, 887, 895
903-904, 912, 920, 928-929, 937, 945, 953-954
962, 970, 978-979, 987, 995
Non-zero exit status: 69
Files=1, Tests=1000, 0 wallclock secs ( 0.18 usr 0.02 sys + 0.29 cusr 0.01 csys = 0.50 CPU)
Result: FAIL
ということで、例えば9.95をfloorを使って小数第2桁で切り捨てようとすると、9.94になってしまうようだ。この事例、大抵の値のパターン(上記例では約97%)ではテストが通ってしまうので、個別にいくつかケースを書いただけでは通ってしまってバグに気付かず、現実にシステムを動かしたときに気付くことになるという点がたちが悪い。
ちなみにこの現象、intを使っても同じことが発生する。上記スクリプトのfloorの部分をintに変えて実行してみれば、同じ結果になることが確認できるであろう。
原因は、おそらくは内部的に浮動小数点を2進数で処理しているとか、それ関連の現象と思われるが、こういう結果が出てしまった以上は、少数第○位以下を切り捨てするような処理をする際には、上記のような広範囲の数値を入れたテストを行うのは必須で、実装もint,floorが使えないので正規表現を使った実装にせざるを得ないと思われる。
以下のコードはインプットされた数字を小数点2桁までで切り捨てるロジックを実装し、0.01から10.00まで0.01づつインクリメントさせながらテストをしたものである。
use strict;
use warnings;
use POSIX qw(floor);
use Test::More;
for (1..1000) {
my $num = $_ / 100;
is((floor($num*100))/100, $num, "$_/100 = $num case");
}
done_testing();
テスト結果
$ prove test.t
test.t .. 1/?
# Failed test '29/100 = 0.29 case'
# at test.t line 8.
# got: '0.28'
# expected: '0.29'
# Failed test '57/100 = 0.57 case'
# at test.t line 8.
# got: '0.56'
# expected: '0.57'
# Failed test '58/100 = 0.58 case'
# at test.t line 8.
# got: '0.57'
# expected: '0.58'
# Failed test '113/100 = 1.13 case'
# at test.t line 8.
# got: '1.12'
# expected: '1.13'
# Failed test '114/100 = 1.14 case'
# at test.t line 8.
# got: '1.13'
# expected: '1.14'
# Failed test '115/100 = 1.15 case'
# at test.t line 8.
# got: '1.14'
# expected: '1.15'
# Failed test '116/100 = 1.16 case'
# at test.t line 8.
# got: '1.15'
# expected: '1.16'
# Failed test '201/100 = 2.01 case'
# at test.t line 8.
# got: '2'
# expected: '2.01'
# Failed test '203/100 = 2.03 case'
# at test.t line 8.
# got: '2.02'
# expected: '2.03'
# Failed test '205/100 = 2.05 case'
# at test.t line 8.
# got: '2.04'
# expected: '2.05'
# Failed test '207/100 = 2.07 case'
# at test.t line 8.
# got: '2.06'
# expected: '2.07'
# Failed test '226/100 = 2.26 case'
# at test.t line 8.
# got: '2.25'
# expected: '2.26'
# Failed test '228/100 = 2.28 case'
# at test.t line 8.
# got: '2.27'
# expected: '2.28'
# Failed test '230/100 = 2.3 case'
# at test.t line 8.
# got: '2.29'
# expected: '2.3'
# Failed test '232/100 = 2.32 case'
# at test.t line 8.
# got: '2.31'
# expected: '2.32'
# Failed test '251/100 = 2.51 case'
# at test.t line 8.
# got: '2.5'
# expected: '2.51'
# Failed test '253/100 = 2.53 case'
# at test.t line 8.
# got: '2.52'
# expected: '2.53'
# Failed test '255/100 = 2.55 case'
# at test.t line 8.
# got: '2.54'
# expected: '2.55'
# Failed test '402/100 = 4.02 case'
# at test.t line 8.
# got: '4.01'
# expected: '4.02'
# Failed test '406/100 = 4.06 case'
# at test.t line 8.
# got: '4.05'
# expected: '4.06'
# Failed test '410/100 = 4.1 case'
# at test.t line 8.
# got: '4.09'
# expected: '4.1'
# Failed test '414/100 = 4.14 case'
# at test.t line 8.
# got: '4.13'
# expected: '4.14'
# Failed test '427/100 = 4.27 case'
# at test.t line 8.
# got: '4.26'
# expected: '4.27'
# Failed test '431/100 = 4.31 case'
# at test.t line 8.
# got: '4.3'
# expected: '4.31'
# Failed test '435/100 = 4.35 case'
# at test.t line 8.
# got: '4.34'
# expected: '4.35'
# Failed test '439/100 = 4.39 case'
# at test.t line 8.
# got: '4.38'
# expected: '4.39'
# Failed test '452/100 = 4.52 case'
# at test.t line 8.
# got: '4.51'
# expected: '4.52'
# Failed test '456/100 = 4.56 case'
# at test.t line 8.
# got: '4.55'
# expected: '4.56'
# Failed test '460/100 = 4.6 case'
# at test.t line 8.
# got: '4.59'
# expected: '4.6'
# Failed test '464/100 = 4.64 case'
# at test.t line 8.
# got: '4.63'
# expected: '4.64'
# Failed test '477/100 = 4.77 case'
# at test.t line 8.
# got: '4.76'
# expected: '4.77'
# Failed test '481/100 = 4.81 case'
# at test.t line 8.
# got: '4.8'
# expected: '4.81'
# Failed test '485/100 = 4.85 case'
# at test.t line 8.
# got: '4.84'
# expected: '4.85'
# Failed test '489/100 = 4.89 case'
# at test.t line 8.
# got: '4.88'
# expected: '4.89'
# Failed test '502/100 = 5.02 case'
# at test.t line 8.
# got: '5.01'
# expected: '5.02'
# Failed test '506/100 = 5.06 case'
# at test.t line 8.
# got: '5.05'
# expected: '5.06'
# Failed test '510/100 = 5.1 case'
# at test.t line 8.
# got: '5.09'
# expected: '5.1'
# Failed test '803/100 = 8.03 case'
# at test.t line 8.
# got: '8.02'
# expected: '8.03'
# Failed test '804/100 = 8.04 case'
# at test.t line 8.
# got: '8.03'
# expected: '8.04'
# Failed test '812/100 = 8.12 case'
# at test.t line 8.
# got: '8.11'
# expected: '8.12'
# Failed test '820/100 = 8.2 case'
# at test.t line 8.
# got: '8.19'
# expected: '8.2'
# Failed test '828/100 = 8.28 case'
# at test.t line 8.
# got: '8.27'
# expected: '8.28'
# Failed test '829/100 = 8.29 case'
# at test.t line 8.
# got: '8.28'
# expected: '8.29'
# Failed test '837/100 = 8.37 case'
# at test.t line 8.
# got: '8.36'
# expected: '8.37'
# Failed test '845/100 = 8.45 case'
# at test.t line 8.
# got: '8.44'
# expected: '8.45'
# Failed test '853/100 = 8.53 case'
# at test.t line 8.
# got: '8.52'
# expected: '8.53'
# Failed test '854/100 = 8.54 case'
# at test.t line 8.
# got: '8.53'
# expected: '8.54'
# Failed test '862/100 = 8.62 case'
# at test.t line 8.
# got: '8.61'
# expected: '8.62'
# Failed test '870/100 = 8.7 case'
# at test.t line 8.
# got: '8.69'
# expected: '8.7'
# Failed test '878/100 = 8.78 case'
# at test.t line 8.
# got: '8.77'
# expected: '8.78'
# Failed test '879/100 = 8.79 case'
# at test.t line 8.
# got: '8.78'
# expected: '8.79'
# Failed test '887/100 = 8.87 case'
# at test.t line 8.
# got: '8.86'
# expected: '8.87'
# Failed test '895/100 = 8.95 case'
# at test.t line 8.
# got: '8.94'
# expected: '8.95'
# Failed test '903/100 = 9.03 case'
# at test.t line 8.
# got: '9.02'
# expected: '9.03'
# Failed test '904/100 = 9.04 case'
# at test.t line 8.
# got: '9.03'
# expected: '9.04'
# Failed test '912/100 = 9.12 case'
# at test.t line 8.
# got: '9.11'
# expected: '9.12'
# Failed test '920/100 = 9.2 case'
# at test.t line 8.
# got: '9.19'
# expected: '9.2'
# Failed test '928/100 = 9.28 case'
# at test.t line 8.
# got: '9.27'
# expected: '9.28'
# Failed test '929/100 = 9.29 case'
# at test.t line 8.
# got: '9.28'
# expected: '9.29'
# Failed test '937/100 = 9.37 case'
# at test.t line 8.
# got: '9.36'
# expected: '9.37'
# Failed test '945/100 = 9.45 case'
# at test.t line 8.
# got: '9.44'
# expected: '9.45'
# Failed test '953/100 = 9.53 case'
# at test.t line 8.
# got: '9.52'
# expected: '9.53'
# Failed test '954/100 = 9.54 case'
# at test.t line 8.
# got: '9.53'
# expected: '9.54'
# Failed test '962/100 = 9.62 case'
# at test.t line 8.
# got: '9.61'
# expected: '9.62'
# Failed test '970/100 = 9.7 case'
# at test.t line 8.
# got: '9.69'
# expected: '9.7'
# Failed test '978/100 = 9.78 case'
# at test.t line 8.
# got: '9.77'
# expected: '9.78'
# Failed test '979/100 = 9.79 case'
# at test.t line 8.
# got: '9.78'
# expected: '9.79'
# Failed test '987/100 = 9.87 case'
# at test.t line 8.
# got: '9.86'
# expected: '9.87'
# Failed test '995/100 = 9.95 case'
# at test.t line 8.
# got: '9.94'
# expected: '9.95'
# Looks like you failed 69 tests of 1000.
test.t .. Dubious, test returned 69 (wstat 17664, 0x4500)
Failed 69/1000 subtests
Test Summary Report
-------------------
test.t (Wstat: 17664 Tests: 1000 Failed: 69)
Failed tests: 29, 57-58, 113-116, 201, 203, 205, 207
226, 228, 230, 232, 251, 253, 255, 402
406, 410, 414, 427, 431, 435, 439, 452
456, 460, 464, 477, 481, 485, 489, 502
506, 510, 803-804, 812, 820, 828-829, 837
845, 853-854, 862, 870, 878-879, 887, 895
903-904, 912, 920, 928-929, 937, 945, 953-954
962, 970, 978-979, 987, 995
Non-zero exit status: 69
Files=1, Tests=1000, 0 wallclock secs ( 0.18 usr 0.02 sys + 0.29 cusr 0.01 csys = 0.50 CPU)
Result: FAIL
ということで、例えば9.95をfloorを使って小数第2桁で切り捨てようとすると、9.94になってしまうようだ。この事例、大抵の値のパターン(上記例では約97%)ではテストが通ってしまうので、個別にいくつかケースを書いただけでは通ってしまってバグに気付かず、現実にシステムを動かしたときに気付くことになるという点がたちが悪い。
ちなみにこの現象、intを使っても同じことが発生する。上記スクリプトのfloorの部分をintに変えて実行してみれば、同じ結果になることが確認できるであろう。
原因は、おそらくは内部的に浮動小数点を2進数で処理しているとか、それ関連の現象と思われるが、こういう結果が出てしまった以上は、少数第○位以下を切り捨てするような処理をする際には、上記のような広範囲の数値を入れたテストを行うのは必須で、実装もint,floorが使えないので正規表現を使った実装にせざるを得ないと思われる。
2015年2月16日月曜日
MySQLのInnoDBにおけるロックの挙動を徹底検証してみた
MySQLのInnoDBにおけるロックの挙動に関して、直感的な理解と反する挙動が度々確認されたので、一旦徹底的に検証をしてここに挙動をまとめておこうと思う。なお、MySQLのバージョンは5.1.61である。
まず準備として、下記のテーブルとデータを使用して検証を行う。
まずはロックに関する基本的な内容のまとめ。
・ロックの種類
共有ロック
排他ロック
・ロックの粒度
行ロック
テーブルロック
ロックには排他ロックと共有ロックの2種類がある。
・排他ロック
データ更新の際に利用されるロック
該当レコードに対し、ひとつのプロセスのみ獲得できる
排他ロックがかかっているレコードに対しては、排他ロックを獲得することはできない
排他ロックがかかっているレコードに対して更新クエリを実行することはできない
排他ロックのクエリ例：
select * from test_lock where id=3 for update;
・共有ロック
読み込みの際に利用されるロック
該当レコードに対し、複数のプロセスが獲得できる
共有ロックがかかっているレコードに対して排他ロックを獲得することはできない
共有ロックがかかっているレコードに対して更新クエリを実行することはできない
共有ロックのクエリ例：
select * from test_lock where id=3 lock in share mode;
つまり、該当レコードの排他ロックを獲得することができれば、レコードの更新が可能だが、共有ロックを獲得できたとしてもレコードの更新ができるとは限らない（他のトランザクションもロックを獲得しているかもしれないから）
・共有ロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> begin;
共有ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
共有ロックされたデータに対して共有ロックを掛けることは可能
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
共有ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
共有ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・排他ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
terminal2
mysql> begin;
排他ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
排他ロックされたデータに対して共有ロックを掛けることはできない
mysql> select * from test_lock where id=3 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ロックの粒度にはテーブルロックと行ロックの2種類がある。
・テーブルロック
オーバーヘッドが低い
書き込み中は他の読み取り、書き込み操作がすべて拒否される
MyISAMはテーブルロック
・行ロック
並行性が高いがオーバーヘッドも高い
行ロックはストレージエンジンで実装されている
・テーブルロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
5 rows in set (0.00 sec)
terminal2
排他ロックがテーブル全体にかかっていてもselectはできる
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがテーブル全体にかかっていると、特定レコードへの排他ロックは取得できない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、特定レコードの更新を実施することができない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、新たなレコードを挿入することができない
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・行ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
排他ロックがかかっているレコードへのselectは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して排他ロックは獲得できないが、かかっていないものに対しての排他ロックは獲得できる
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test_lock where id=2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して更新はできないが、かかっていないものに対しての更新はできる
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
排他ロックが掛かっていても、新たなレコードの挿入はできる
mysql> rollback;
mysql> begin;
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
上記を踏まえた上で、ここからが本題。
検索クエリのインデックスが効いていないと、テーブルロックがかかってしまうことがわかった。今回の検証用テーブルで、インデックスのあるカラムとないカラムを用意したので、それぞれを検索条件に入れたクエリでどう挙動が変わるのかを確認しよう。
・インデックスがあるケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
インデックスが効いているカラムを使ってのselect..for updateでは、
他のトランザクションは排他ロックが獲得されているレコード以外の更新や挿入は可能
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
・インデックスがないケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまでは想定通り。排他ロックが獲得されたレコードの更新ができないのは当然。ところが・・
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
なんと全レコードの更新ができないという状況が発生している。さらに・・
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
新たなレコードの挿入すらできない。つまりこれはテーブルロック
インデックスを使った検索ができないクエリによる排他ロックは、テーブルロックになる。
続いて、範囲検索によるロックの獲得に関しても、直感的な理解とは異なる挙動をする。
・範囲検索を行った場合のロックの挙動
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまではわかる。今回のselect..for updateによる抽出レコードだから。問題はここから。
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
id:1,4はselect..for updateの抽出対象でないにもかかわらずロックが掛かっている
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:5の更新と、新規レコード挿入は問題なくできたことからテーブルロックではないことがわかる
explainを実行した限りではrowsは2行となっているが、それ以外のレコードにもロックが掛かっていることになる。
mysql> explain select * from test_lock where id < 4 and id <> 1;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
・範囲検索を行った場合のロックの挙動2
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
このロックは想定通り。
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
クエリで除外条件に指定しているにもかかわらず、ロック対象になっている。
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
これもクエリに取得条件には含まれないがロック対象になっている。
mysql> update test_lock set index_ari=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:4,5の更新と新規レコード挿入は問題なく出来た。
ロックの取得は範囲検索では広めに（仮説としては第1検索条件の範囲+境界条件）行われる
・ロックの範囲を確認する方法
http://blog.kamipo.net/entry/2013/12/03/235900
記載の説明では一体どうログを読めばよいのかわからなかったが、とりあえず
mysql> CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;
して、ロックを獲得するクエリを発行後
mysql> SHOW ENGINE INNODB STATUS\G;
することで手がかりとなるログが現れるようだ。ではいろいろ試してみよう。
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。「0: len 4; hex 00000001」と書かれているのがid番号だろうか？
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 23 sec
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
1 row lock(s)という記載と1つのRecord lockのデータが記載されている。
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 13 sec,
2 lock struct(s), heap size 376, 1 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
ただし先の実験ではid:3,5にはロックが掛かっておらず、データの更新ができたことが確認されている
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
4 lock struct(s), heap size 1248, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。
テーブルロックの時とログの出方がほぼ同じであることからテーブルロックになっていると推定できる
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 3 sec,
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
4 row lock(s)という記載と4つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3,4にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
2 lock struct(s), heap size 376, 4 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 4 sec,
2 lock struct(s), heap size 376, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
今後の課題
InnoDBのREPEATABLE READにおけるLocking Readについての注意点
にあるlost updateは時間のあるときに追加で確認しておこうかと。
#terminal1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#terminal2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
#terminal1
mysql> update test_lock set index_ari=index_ari+1 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
順番にupdateを実行すると、最初のupdateでロックが掛かって次のトランザクションは更新ができなくなるようだ。
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
即下記を実行。
#terminal1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
terminal1でコミットをするとterminal2の待ち状態のupdateが通る
#terminal2
Query OK, 1 row affected (3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_lock;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
ということで、同時updateはちょっとでも速い一方のトランザクションがロックを獲得するので、その更新がコミットされた後、もう一方のトランザクションの更新がなされるので、MySQL5.1.61では特にlost updateの不具合は生じないことがわかった。
参考文献
実践ハイパフォーマンスMySQL 第2版
まず準備として、下記のテーブルとデータを使用して検証を行う。
CREATE TABLE `test_lock` (
`id` int(10) unsigned NOT NULL,
`index_ari` int(10) unsigned NOT NULL,
`index_nasi` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`index_ari`)
) ENGINE=InnoDB;
insert test_lock (id,index_ari,index_nasi) values
(1,1,1),
(2,0,2),
(3,3,0),
(4,4,4),
(5,5,5);
まずはロックに関する基本的な内容のまとめ。
・ロックの種類
共有ロック
排他ロック
・ロックの粒度
行ロック
テーブルロック
ロックには排他ロックと共有ロックの2種類がある。
・排他ロック
データ更新の際に利用されるロック
該当レコードに対し、ひとつのプロセスのみ獲得できる
排他ロックがかかっているレコードに対しては、排他ロックを獲得することはできない
排他ロックがかかっているレコードに対して更新クエリを実行することはできない
排他ロックのクエリ例：
select * from test_lock where id=3 for update;
・共有ロック
読み込みの際に利用されるロック
該当レコードに対し、複数のプロセスが獲得できる
共有ロックがかかっているレコードに対して排他ロックを獲得することはできない
共有ロックがかかっているレコードに対して更新クエリを実行することはできない
共有ロックのクエリ例：
select * from test_lock where id=3 lock in share mode;
つまり、該当レコードの排他ロックを獲得することができれば、レコードの更新が可能だが、共有ロックを獲得できたとしてもレコードの更新ができるとは限らない（他のトランザクションもロックを獲得しているかもしれないから）
・共有ロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> begin;
共有ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
共有ロックされたデータに対して共有ロックを掛けることは可能
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
共有ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
共有ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・排他ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
terminal2
mysql> begin;
排他ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
排他ロックされたデータに対して共有ロックを掛けることはできない
mysql> select * from test_lock where id=3 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ロックの粒度にはテーブルロックと行ロックの2種類がある。
・テーブルロック
オーバーヘッドが低い
書き込み中は他の読み取り、書き込み操作がすべて拒否される
MyISAMはテーブルロック
・行ロック
並行性が高いがオーバーヘッドも高い
行ロックはストレージエンジンで実装されている
・テーブルロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
5 rows in set (0.00 sec)
terminal2
排他ロックがテーブル全体にかかっていてもselectはできる
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがテーブル全体にかかっていると、特定レコードへの排他ロックは取得できない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、特定レコードの更新を実施することができない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、新たなレコードを挿入することができない
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・行ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
排他ロックがかかっているレコードへのselectは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して排他ロックは獲得できないが、かかっていないものに対しての排他ロックは獲得できる
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test_lock where id=2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して更新はできないが、かかっていないものに対しての更新はできる
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
排他ロックが掛かっていても、新たなレコードの挿入はできる
mysql> rollback;
mysql> begin;
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
上記を踏まえた上で、ここからが本題。
検索クエリのインデックスが効いていないと、テーブルロックがかかってしまうことがわかった。今回の検証用テーブルで、インデックスのあるカラムとないカラムを用意したので、それぞれを検索条件に入れたクエリでどう挙動が変わるのかを確認しよう。
・インデックスがあるケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
インデックスが効いているカラムを使ってのselect..for updateでは、
他のトランザクションは排他ロックが獲得されているレコード以外の更新や挿入は可能
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
・インデックスがないケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまでは想定通り。排他ロックが獲得されたレコードの更新ができないのは当然。ところが・・
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
なんと全レコードの更新ができないという状況が発生している。さらに・・
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
新たなレコードの挿入すらできない。つまりこれはテーブルロック
インデックスを使った検索ができないクエリによる排他ロックは、テーブルロックになる。
続いて、範囲検索によるロックの獲得に関しても、直感的な理解とは異なる挙動をする。
・範囲検索を行った場合のロックの挙動
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまではわかる。今回のselect..for updateによる抽出レコードだから。問題はここから。
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
id:1,4はselect..for updateの抽出対象でないにもかかわらずロックが掛かっている
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:5の更新と、新規レコード挿入は問題なくできたことからテーブルロックではないことがわかる
explainを実行した限りではrowsは2行となっているが、それ以外のレコードにもロックが掛かっていることになる。
mysql> explain select * from test_lock where id < 4 and id <> 1;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
・範囲検索を行った場合のロックの挙動2
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
このロックは想定通り。
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
クエリで除外条件に指定しているにもかかわらず、ロック対象になっている。
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
これもクエリに取得条件には含まれないがロック対象になっている。
mysql> update test_lock set index_ari=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:4,5の更新と新規レコード挿入は問題なく出来た。
ロックの取得は範囲検索では広めに（仮説としては第1検索条件の範囲+境界条件）行われる
・ロックの範囲を確認する方法
http://blog.kamipo.net/entry/2013/12/03/235900
記載の説明では一体どうログを読めばよいのかわからなかったが、とりあえず
mysql> CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;
して、ロックを獲得するクエリを発行後
mysql> SHOW ENGINE INNODB STATUS\G;
することで手がかりとなるログが現れるようだ。ではいろいろ試してみよう。
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。「0: len 4; hex 00000001」と書かれているのがid番号だろうか？
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 23 sec
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
1 row lock(s)という記載と1つのRecord lockのデータが記載されている。
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 13 sec,
2 lock struct(s), heap size 376, 1 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
ただし先の実験ではid:3,5にはロックが掛かっておらず、データの更新ができたことが確認されている
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
4 lock struct(s), heap size 1248, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。
テーブルロックの時とログの出方がほぼ同じであることからテーブルロックになっていると推定できる
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 3 sec,
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
4 row lock(s)という記載と4つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3,4にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
2 lock struct(s), heap size 376, 4 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 4 sec,
2 lock struct(s), heap size 376, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
今後の課題
InnoDBのREPEATABLE READにおけるLocking Readについての注意点
にあるlost updateは時間のあるときに追加で確認しておこうかと。
#terminal1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#terminal2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
#terminal1
mysql> update test_lock set index_ari=index_ari+1 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
順番にupdateを実行すると、最初のupdateでロックが掛かって次のトランザクションは更新ができなくなるようだ。
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
即下記を実行。
#terminal1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
terminal1でコミットをするとterminal2の待ち状態のupdateが通る
#terminal2
Query OK, 1 row affected (3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_lock;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
ということで、同時updateはちょっとでも速い一方のトランザクションがロックを獲得するので、その更新がコミットされた後、もう一方のトランザクションの更新がなされるので、MySQL5.1.61では特にlost updateの不具合は生じないことがわかった。
参考文献
実践ハイパフォーマンスMySQL 第2版
2015年1月11日日曜日
Perlの文字化け、これ読んだらよくわかった
日本語が含まれる文字列を扱うとき、必ずと言っていいほど文字化けに悩まされ、その辺の扱いがよくわからず困っていたのだが、これ読んだらよくわかった。
http://tech.voyagegroup.com/archives/465806.html
なるほど。「flagged UTF8」なんて言葉があるからわかりにくいんだな。
つまり文字の状態は2種類あると。
１つはUTF8やEUCなど、各文字コードで書かれた文字列。
もう1つはPerl内だけで使用されている専用の内部文字列。
後者をflagged UTF8とか紛らわしい言い方をするから混乱するわけで、これは我々には読めない機械語だとでも思っておけばいいんだよ。
だから文字列の処理の仕方は、
#外から来た各文字コードで書かれた文字をPerl処理用の内部文字列に変換
my $inner_str = Encode::decode("utf8", $str);
#もろもろ$inner_strに対して処理をする
#Perl内で処理した内部文字列を各文字コードに変換
my $str = Encode::encode("utf8", $inner_str);
となるわけだ。でuse utf8とかいう意味不明のおまじないは、Perlプログラム内で書かれた
文字列をPerl内処理用の内部文字列として扱うというものだったと。ようやく分かった。
http://tech.voyagegroup.com/archives/465806.html
なるほど。「flagged UTF8」なんて言葉があるからわかりにくいんだな。
つまり文字の状態は2種類あると。
１つはUTF8やEUCなど、各文字コードで書かれた文字列。
もう1つはPerl内だけで使用されている専用の内部文字列。
後者をflagged UTF8とか紛らわしい言い方をするから混乱するわけで、これは我々には読めない機械語だとでも思っておけばいいんだよ。
だから文字列の処理の仕方は、
#外から来た各文字コードで書かれた文字をPerl処理用の内部文字列に変換
my $inner_str = Encode::decode("utf8", $str);
#もろもろ$inner_strに対して処理をする
#Perl内で処理した内部文字列を各文字コードに変換
my $str = Encode::encode("utf8", $inner_str);
となるわけだ。でuse utf8とかいう意味不明のおまじないは、Perlプログラム内で書かれた
文字列をPerl内処理用の内部文字列として扱うというものだったと。ようやく分かった。
2014年11月12日水曜日
zgrepやzcat | grepでBinary file (standard input) matchesが出た場合の対処法
hoge_log.yyyymmdd.tar.gzみたいなログファイルを「zgrep 検索文字列 hoge_log.yyyymmdd.tar.gz」やら
「zcat hoge_log.yyyymmdd.tar.gz | grep 検索文字列」なんかで検索しようとしたら、結果が
「Binary file (standard input) matches」と出て困った。なんか解決法がないのか検索してみたら、
このページを見て解決。
http://nobuneko.com/blog/archives/2013/04/linux_grep_binary_files_text.html
つまりはgrepが対象をバイナリファイルとみなしているがためにこの現象が起きるみたい。
解決法としては、-aオプションを付ければよいようだ。これでテキストファイルとみなされ、
検索結果が表示されるようになる。つまりは、
zgrep -a 検索文字列 hoge_log.yyyymmdd.tar.gz
や
zcat hoge_log.yyyymmdd.tar.gz | grep -a 検索文字列
とやって検索すればOK。
「zcat hoge_log.yyyymmdd.tar.gz | grep 検索文字列」なんかで検索しようとしたら、結果が
「Binary file (standard input) matches」と出て困った。なんか解決法がないのか検索してみたら、
このページを見て解決。
http://nobuneko.com/blog/archives/2013/04/linux_grep_binary_files_text.html
つまりはgrepが対象をバイナリファイルとみなしているがためにこの現象が起きるみたい。
解決法としては、-aオプションを付ければよいようだ。これでテキストファイルとみなされ、
検索結果が表示されるようになる。つまりは、
zgrep -a 検索文字列 hoge_log.yyyymmdd.tar.gz
や
zcat hoge_log.yyyymmdd.tar.gz | grep -a 検索文字列
とやって検索すればOK。
2014年10月31日金曜日
【Perl】Mooseの使い方
Mooseとは簡単にクラスを作ることができるモジュールのようだ。newとかは実装不要。
package Foo;
use Moose;
extends 'FooParent'; #これで親モジュールを継承できる
has name => { #アクセサを定義する関数
is => 'rw', #rwは読み書きができ、roは読み取りだけ($Foo->name,$Foo->name("hoge"))
isa => 'Str', #型の指定を行う
default => "Bob", #デフォルト値を指定できる。sub {Hoge->config}のようにサブルーチンも可
required => 1, #必須パラメータの場合は1を指定する
}
sub hoge { #メソッドの定義は普通に行う
print "My name is".shift->name."\n";
}
参考：
http://perldoc.jp/docs/modules/Moose/Manual.pod
package Foo;
use Moose;
extends 'FooParent'; #これで親モジュールを継承できる
has name => { #アクセサを定義する関数
is => 'rw', #rwは読み書きができ、roは読み取りだけ($Foo->name,$Foo->name("hoge"))
isa => 'Str', #型の指定を行う
default => "Bob", #デフォルト値を指定できる。sub {Hoge->config}のようにサブルーチンも可
required => 1, #必須パラメータの場合は1を指定する
}
sub hoge { #メソッドの定義は普通に行う
print "My name is".shift->name."\n";
}
参考：
http://perldoc.jp/docs/modules/Moose/Manual.pod
【Perl】AUTOLOADの使用例
定義されていない関数を呼び出すとこのAUTOLOADというのが呼び出される便利な関数。
cpanのモジュールを拡張して独自モジュールを作るような使い方ができる。
Foo.pm
#########################################################
package Foo;$
use strict;
use warnings;
sub AUTOLOAD {
> our $AUTOLOAD;
> my (@args) = @_;
> print "autoload start..function_name:$AUTOLOAD\n";
> print "argument is (".join(',',@args).")\n";
}
1;
#########################################################
$ perl -MFoo -e 'Foo::test("a","b","c")';
autoload start..function_name:Foo::test
argument is (a,b,c)
参考：
サンプルコードによるPerl入門 サブルーチンのオートロード AUTOLOAD
http://perldoc.jp/docs/perl/5.8.0/AutoLoader.pod
cpanのモジュールを拡張して独自モジュールを作るような使い方ができる。
Foo.pm
#########################################################
package Foo;$
use strict;
use warnings;
sub AUTOLOAD {
> our $AUTOLOAD;
> my (@args) = @_;
> print "autoload start..function_name:$AUTOLOAD\n";
> print "argument is (".join(',',@args).")\n";
}
1;
#########################################################
$ perl -MFoo -e 'Foo::test("a","b","c")';
autoload start..function_name:Foo::test
argument is (a,b,c)
参考：
サンプルコードによるPerl入門 サブルーチンのオートロード AUTOLOAD
http://perldoc.jp/docs/perl/5.8.0/AutoLoader.pod
登録:
投稿 (Atom)