「外部キー Night」に参加しました

2015/2/13(金) 「外部キー Night」に参加してきました。

connpass
http://connpass.com/event/11463/

場所は千駄ヶ谷（代々木）のピクシブ株式会社さんです。
参加者は60人くらいでしょうか。

・・・この勉強会のタイトル！そしてピンポイントなテーマｗ
惹きつけられずにはいられない！

そんな私も、SQLアンチパターン読書会で4章「キーレスエントリ」の紹介を担当したということもあり、外部キーには少し思い入れがある一人なのです。

外部キーNightのお供に、書籍「SQLアンチパターン」の４章、「キーレスエントリー」をお一つ、いかがですか～ / SQLアンチパターン読書会 「キーレスエントリー」 に参加しました http://t.co/Z116mYUDyI #sqlap #fk_night

— makopi23 (@makopi23) 2015, 2月 12

以下、個人メモ。

---

■ 外部キー制約に伴うロックの小話　@ichirin2501さん

外部キー制約に伴う小話 from ichirin2501

外部キー制約に起因するデッドロック発生のメカニズムがいくつか紹介されていて、実に興味深い。
ということで、実際にやってみないと気がすまないタチなので実機で試してみた。
環境はWindows7(x64)のMySQL5.1.37(InnoDB)です。

■ P.8 「外部キー制約によるロック（基本編）」の再現

ちなみに上がトランザクションA（user1)、下がトランザクションB(user2)です。

(1)
トランザクションA(user1)とトランザクションB(user2)を開始させる。


(2)
次に、トランザクションA(user1)側で player_item テーブルにINSERTを実行する。
これで外部キー制約される親側の player.id(100) と item.id(2000) に対して共有ロックが獲得されたはず。


(3)
次に、トランザクションB(user2)側で player.id(100) をキーに指定して、SELECTで排他ロックを獲得しようとする。
すると、(2)でplyaer.id(100)に共有ロックが取られているため、トランザクションB(user2)側のSELECTが確かに待たされる・・・


(4)
トランザクションA(user1)側でUpdateを実行しようとすると、デッドロック発生！
即座にトランザクションB(user2)側が自動的にロールバックされ、トランザクションA(user1)側のUpdateが成功します。


ちなみに自動でロールバックされたこの現象ですが、InnoDBはデッドロック検知機構が搭載されているらしく、デッドロックが発生した場合はどれかのトランザクションが安全にロールバックされるそうです。
今回は確かにトランザクションB(user2)側が即座にロールバックされましたね。


■ P.13 「外部キー制約によるロック（シャドーロック編）」の再現
シャドーロックも実際に試してみる。

■ case1： item -> player の順でindex定義

(1)
player_itemテーブルのcreate時に、インデックスを item -> player の順で作成する。


(2)
トランザクションA(user1)とトランザクションB(user2)を開始させる。
次に、トランザクションA(user1)側で player.id(100) の排他ロックを取る。


(3)
次に、トランザクションB(user2)側でplayerテーブルにiNSERT文を発行する。
すると、(2)で player.id(100)に排他ロックが取られているため、待たされる。
この時、シャドーロックにより item.id(2000)に対しても共有ロックが獲得済みとなる。


(4)
トランザクションA(user1)側で item.id(2000) の排他ロックを取ろうとすると、(3)のシャドーロックにより item.id(2000)の共有ロックが先に取られているため待たされる。
⇒　デッドロック発生！

即座にトランザクションA(user1)が自動でロールバックされ、トランザクションB(user2)側で待たされていたInsertが実行されました。

インデックスの定義順序が原因でデッドロックになってしまうのですね～・・・


■ P.22 「補足： ロックは食いつく」 ＆ 「INSERTでDuplicateEntryになったとき」の再現

(1)
Uniq制限のあるテーブル player_token を作成する。
インデックスは id -> token -> itemの順とする。


(2)
トランザクションA(user1)で、player_tokenテーブルにINSERTし、排他ロックを獲得する。


(3)
トランザクションB(user2)側でもplayer_tokenテーブルにINSERTしようとするが、(2)の排他ロックにより待たされる。


(4)
トランザクションA(user1)側をコミットして排他ロックを開放すると、トランザクションB(user2)側で待たされていたINSERT文を実行しようとする。
しかし、カラムtokenのUNIQUE制限にひっかかり、DuplicateEntryのエラーになる。
ここで注意すべきなのは、DuplicateEntryになったものの、トランザクションは継続しているという点。
更に、インデックス定義が id->tokenの順のため、シャドーロックによりplayer.id(200)の共有ロックは獲得されたままになっている。


(5)
トランザクションA(user1)側でplayer.id(200)の排他ロックを取ろうとすると、(4)のシャドーロック（共有ロック）が獲得済みのため、待たされる・・・


MySQLのロック機構、実に興味深い挙動です。
上記の写経で使ったSQLもついでにアップしておきます。
20150213_fknight_sql1.txt
---


■ 我々(主語が大きい)は何故MySQLで外部キーを使わないのか　@songmuさん

【↑をクリックするとスライドに飛びます。】

外部キーを使わない理由をいくつか紹介されてます。

外部キー制約を使うとパーティショニングができない、という話はSQLアンチパターン読書会の４章「キーレスエントリ」について議論しているときにも出てきましたね。
冒頭の私のツイート先のブログにもチラッと書いてあります。

あと、外部キーを定義すると自動的にインデックスが貼られる点についても言及されてます。
インデックス肥大を避けるために外部キーを使わない、という判断もありえるとのこと。

外部キー制約を使わないんだったら、その代わり、整合性を担保したテストデータを生成するような仕組みをきちんと用意する必要がある、という点も、なるほど～ですね。

外部キーを使わない理由って、けっこーあるもんですねー。勉強になりました。


■ 我々は何故RDBMSを使うのか　@kamipoさん

【↑をクリックするとスライドに飛びます。】

SQLアンチパターン読書会で @t_wada さんから何回か @kamipo さんの話題が出たことがあるので、お名前は知ってましたが、この日が初見。
スマートで、イケメンで、茶髪混じりの今風の若者といった感じ。裏山氏。
ですが話を聞いてみると知見が深く、天才肌、というイメージのエンジニアですね。

そんな@kamipo 氏から「SQLアンチパターンを読め」的な発言が飛び出し、その発言がTLにいくつか流れて・・・

この流れなら言える。外部キー Night に参加している皆様も参加していない皆様も、何卒『SQL アンチパターン』をよろしくお願いします！！ http://t.co/8PDkv5V53Z #fk_night

— Takuto Wada (@t_wada) 2015, 2月 13

MySQLは即時制約しか対応してないらしく、ステートメント毎にチェックが走ってしまうそうです。
遅延制約が使えないとなると、テストデータの投入とか大変そうですな。

全件舐めるSQLに対する皮肉、カスケード削除による大量件数の物理削除を裏でDBMSにやらせる案、レビューおじさんの話など、ユニークでユーモアある内容でした。

とても勉強になるスライドです。

---

■ LT
３人の講演のあと、２人のLTがありました。

１件目は@karupaneruraさんによる「FKアンチパターン (LT)」、こちらはスライドはまだ公開されてないっぽいですね。
２件目は以下です。

■ 深い親子関係を、整合性を保ったまま非正規化したい (LT)　@yubaさん

深い親子関係のテーブル設計 from Takuma Miura

深い階層構造によるJOIN地獄を避けるための工夫が紹介されています。
複合の主キーにするのではなく、複合のユニークキーにして単一性を持たせ、複合ユニークキーに参照制約を付与するという案。
これはよく考えましたね。SQLにすると、こんな感じですかね。

create table user ( id BIGINT UNSIGNED PRIMARY KEY, text varchar(20) ); create table blog ( id BIGINT UNSIGNED PRIMARY KEY, id_user BIGINT UNSIGNED, text varchar(20), unique(id, id_user), foreign key (id_user) references user(id) ); create table article ( id BIGINT UNSIGNED PRIMARY KEY, id_blog BIGINT UNSIGNED, id_user BIGINT UNSIGNED, text varchar(20), unique(id, id_blog, id_user), foreign key (id_blog, id_user) references blog(id, id_user) ); create table comment ( id BIGINT UNSIGNED PRIMARY KEY, id_article BIGINT UNSIGNED, id_blog BIGINT UNSIGNED, id_user BIGINT UNSIGNED, text varchar(20), unique(id_article, id_blog, id_user), foreign key (id_article, id_blog, id_user) references article(id, id_blog, id_user) );

これはいつか使えるかもしれない・・・

---

★感想：
MySQLに特化した内容が多かったなぁ、と思うのと同時に、DBMSの違いでこうもいろいろ意識せにゃならんのか、と驚かされた。
あと、家に帰っていろいろスライド見返してようやく理解できた内容が多かったです。
知らない単語も多かったし、そもそもMySQLの知識も経験も足りない・・・
写経してみてようやく腑に落ちたというか。
参加者のみなさん、聴いてその場ですぐ理解できてたのかな・・・？そうなら凄いなぁ。

SQLアンチパターンの「キーレスエントリ」を読んで私は完全に「外部キー付ける派」だったんですが、この日の話をいろいろ聞いて、外部キーを付けない、という判断も現実には結構あるんだなぁ、と知った。

外部キーという狭いテーマなのに、いろんなテーマに話題が波及してて、外部キー奥深し。

皆様、ありがとーございました。