Insert ignore into、Select For Update と シュレディンガーの猫

Repeatable Read と シュレディンガーの猫

Repeatable Read のポリシーはシュレディンガーの猫と似ていると思う。つまり、状態が観測されたらそのTransactionで、それは決定事項になるという事である。

• シュレディンガーの猫 http://ja.wikipedia.org/wiki/%E3%82%B7%E3%83%A5%E3%83%AC%E3%83%BC%E3%83%87%E3%82%A3%E3%83%B3%E3%82%AC%E3%83%BC%E3%81%AE%E7%8C%AB

シュレディンガーの猫的には以下である。

• 箱の中身を観測するまでは猫が生きているかいきていないかは決定してなく、どちらの状態もとりうる
• 箱の中身を観測した時点において、猫がいきている状態なのか死んでいる状態なのか確定する

これを Repeatable Read におきかえると以下のようになる

• Transactionないで特定のレコード(猫)の状態を観測するまではそのレコード(猫)はどんな値もとりうる
• Transactionないでなんらかの方法でレコード(猫)の状態を観測したタイミングでレコード(猫)の値が決定される(もちろん更新系の処理をしないとならないけど)。排他/共有LockがとられるのでほかのTransactionからは変更不可能になる)

http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html

Insert Ignore Into , Select for Updateの違い。

この状態が観測されたらというのがわりとみそで、INSERT IGNORE INTOでレコードが存在したとき、UPDATE (SELECT FOR UPDATE)でレコードが存在しなく時もそれぞれ以下のような状態が確定するので共有Lock相当のLockがとられるという事をわすれがちです。

• 存在するレコードに対するINSERT IGNORE INTO : レコードが存在する事が確定、ほかのTransactionからDELETEされないようにロックとる
• 存在しないレコードに対するUPDATE。 レコードが存在しない事が確定、ほかのTransactionからINSERTされないようにロックとる

例えば、以下のようなテーブルがあったとします。

CREATE TABLE `friend_counts` (
  `user_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

このテーブルに対して、レコードがある場合は、countの値をIncrement、なかったら初期データーをつっこむということをしたいとします。

下記がSelect for updateで行うパターンです。

sub increment_friend_count {
  my $user_id = shift;
  my $dbh = _get_dbh;

  my ($affected_row) = $dbh->selectrow_array('UPDATE friend_counts SET count = count + 1 WHERE user_id = ?', undef, $user_id);

  unless ($affected_row) {
    $dbh->do('INSERT INTO friend_counts (user_id, friend_num) values(?, 1)', undef, $user_id);
  }
  $dbh->commit;
}

下記がInsert ignore intoで行うパターンです

sub increment_friend_count {
  my $user_id = shift;
  my $dbh = _get_dbh;

  my $affected_row =  $dbh->do('INSERT IGNORE INTO friend_counts (user_id, count) values(?, 1)', undef, $user_id);
  unless ($affected_row) {
    $dbh->do('UPDATE friend_count set count = count+1 WHERE user_id = ?', undef, $user_id);
  }
  $dhh->commit;
}

これ、ちなみにどちらも問題があります。まず、前者に関しては、UPDATEがからぶった場合、そのレコードに対する共有Lockをとります。ちなみにMYSQLはIndex TreeでLockするので、存在しないレコードに対するlockはGap Lockで実現されます。 なので、レコードが存在しない状態でこのコードが同時に二つのセッションから実行されると、UPDATEの部分で同時にGap Lock (共有Lock) が取得され、その後のInsertでDeadlockします。

INSERT IGNORE INTOも同様に INSERT IGNORE INTOが失敗した時点(affected_rowsが0)の時点でそのレコードが存在している事が確定します。ほかのTransactionからのDELETEを許したりすると、TRANSACTIONないでそのレコードが存在する事を担保できないので、INSERTが失敗したレコードに対して共有LOCKが取得されます。この場合、レコードが有る状態で同時に二つのSessionから実行されると Deadlockします。

これ、どちらも同じように感じますが、以下の問題設定では大きく異なります。

• 大きいサービスでRequestがすごい頻度でくる
• 新しく機能をリリースしたタイミングでテーブルにレコードがあまりうまっていない。

なぜなら、前者のSELECT FOR UPDATEを先に行うバージョンはGAP LOCKなので、Index Treeでその両端にあるレコードまでLockします。極端な話、テーブルが空っぽの状態ではテーブルLockと等しい状態になります。

一方、後者のINSERT IGNORE INTOから先に行うパターンはRowに対する共有Lockでしかないので、影響範囲が限定されます。

空っぽのテーブルに対して、同時に別のユーザーidでこのパターンを実行する以下のサンプルがわかりやすいです。

セッションA

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE friend_counts set count = count + 1 where user_id = 1000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

セッションB

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE friend_counts SET count = count + 1 where user_id = 2000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> INSERT INTO friend_counts (user_id, count) VALUES(2000,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

一方 Insert ignore intoだとこれはもちろん、成功します。

セッションA

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO user_friend_counts (user_id, count) VALUES(1000,1);
Query OK, 0 rows affected (0.00 sec)

セッションB

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO friend_counts (user_id, count) VALUES(2000,1);
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE friend_counts SET count = count + 1 where user_id = 2000;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

なので、サービスリリース直後はINSERT IGNORE INTOの方がよいかなーと思った次第。