mysql1> CREATE DATABASE d1;
mysql1> USE d1; mysql1> CREATE TABLE t1 (num SERIAL, val VARCHAR(32), KEY (val(1))) ENGINE = InnoDB; mysql1> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); mysql1> START TRANSACTION; mysql1> SELECT * FROM t1 WHERE val = 'two' FOR UPDATE;
mysql2> SELECT * FROM t1 WHERE num = 3 LOCK IN SHARE MODE;
valの先頭1文字でインデックスになっているので、"[t]wo"のロックは"[t]hree"も一緒にロックする。
よってnum = 3のロックは不可能。
READ COMMITTED だと後続の接続が num = 3 の行をロック出来るみたいです。
返信削除が、val = 'three' だとやっぱり競合するようです。
どういう原理なんでしょう??
おお、本当ですね。ちょっと調べてみます。
返信削除とりあえず
【READ-COMMITTED】
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 28, OS thread handle 0x7f9b0c2ed700, query id 491 localhost root cleaning up
TABLE LOCK table `d1`.`t1` trx id 18044 lock mode IX
RECORD LOCKS space id 315 page no 4 n bits 72 index `val` of table `d1`.`t1` trx id 18044 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 8; hex 0000000000000002; asc ;;
RECORD LOCKS space id 315 page no 3 n bits 72 index `num` of table `d1`.`t1` trx id 18044 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 0000000000000002; asc ;;
1: len 6; hex 000000004662; asc Fb;;
2: len 7; hex 83000001360121; asc 6 !;;
3: len 3; hex 74776f; asc two;;
【REPEATABLE-READ】
3 lock struct(s), heap size 376, 5 row lock(s)
MySQL thread id 28, OS thread handle 0x7f9b0c2ed700, query id 501 localhost root cleaning up
TABLE LOCK table `d1`.`t1` trx id 18062 lock mode IX
RECORD LOCKS space id 315 page no 4 n bits 72 index `val` of table `d1`.`t1` trx id 18062 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 8; hex 0000000000000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 74; asc t;;
1: len 8; hex 0000000000000003; asc ;;
RECORD LOCKS space id 315 page no 3 n bits 72 index `num` of table `d1`.`t1` trx id 18062 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 0000000000000002; asc ;;
1: len 6; hex 000000004662; asc Fb;;
2: len 7; hex 83000001360121; asc 6 !;;
3: len 3; hex 74776f; asc two;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 0000000000000003; asc ;;
1: len 6; hex 000000004662; asc Fb;;
2: len 7; hex 83000001360132; asc 6 2;;
3: len 5; hex 7468726565; asc three;;
というところまで見ました。