あるあるおハマり大事典 - InnoDBなのに行ロックしないの

drop table if exists t;

create table t (
  iid int
 ,nid int
 ,bid binary(3)
 ,msg varchar(69)
 ,key (iid)
 ,key (bid)
) ENGINE=InnoDB;

insert into t values
 (1,1,1,"ichi"),(2,2,2,"ni"),(3,3,3,"san")
,(4,4,4,"si"),(5,5,5,"go"),(6,6,6,"roku")
;

なテーブルとデータで、2つ端末を用意して update しあいっこしてみます。

まず、これ↓は両方ともupdateが完了してスコっと返ってきます。行レベルロック++

begin;
update t set msg = "t1" where iid = 1;
と
begin;
update t set msg = "t2" where iid = 2;

これ↓は、where句で使っているのが、インデックスがはられていないカラムなので、後発のクエリはすぐ返らず(先発のがcommit or rollback or タイムアウトするまで)待たされます。これも期待通り。

begin;
update t set msg = "t1" where nid = 1;
と
begin;
update t set msg = "t2" where nid = 2;

で、おハマりしたのがこれ↓。intじゃなくてbinary型なんだけどちゃんとインデックスははってあるのになんでか後発のが待たされてしまいます。

begin;
update t set msg = "t1" where bid = 1;
と
begin;
update t set msg = "t2" where bid = 2;

さて、どうして行レベルロックにならずに後発のクエリが待たされるのでしょうか？
答えはこのあとすぐ！！

まだだよー

そろそろいくよー

explain select * from t where bid = 1\G

とかするとわかるんですが、なんと「type: ALL」でフルテーブルスキャンになってます。なのでテーブル全体にロックがかかってると。

フルスキャンになる理由は、数値型→binary型への型変換がかかるせいかなと思います。

なので、

begin;
update t set msg = "zero" where bid = '1\0\0';
と
begin;
update t set msg = "zero" where bid = '2\0\0';

とすると、後発のもすぐに返ってきます。

bid = 1 でもちゃんとマッチするので、型変換のことをずっぽし見落としてました＞＜

今回も id:kazuhooku さんに多大なるご支援、ご協力を賜りましてほんとうにありがとうございました。